rails: Postgres migrations do not respect functional defaults for columns
PostgreSQL allows columns to have functional defaults, but Rails does not appear to support this when using the migrations API. The most notorious example of this is outlined below:
CREATE TABLE a ( t1 TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP ); -- works
CREATE TABLE b ( t1 TIMESTAMPTZ DEFAULT NOW() ); -- works
CREATE TABLE c ( t1 TIMESTAMPTZ DEFAULT 'NOW()' ); -- fails and uses a static timestamp
Tables A and B defined above will work as expected, allowing any new rows to have a proper default value that is calculated at the time of insert. Table C, however, will convert the string ‘now’ to the current time when the table is defined, causing all inserted default values to reflect the static timestamp instead.
When attempting to use the migration API, one might write code such as:
create_table :c do |t|
t.timestamp default: 'NOW()'
end
This unfortunately yields the broken use-case of table C from the SQL above.
Proposed Solution
To ensure backwards-compatibility, database portability, and coverage of cases not listed here, I propose adding an option such as raw_default
that could be used to pass unquoted defaults to the database in the DDL of a migration.
create_table :c do |t|
t.timestamp raw_default: 'NOW()'
end
Which would yield the following DDL:
CREATE TABLE c ( t1 TIMESTAMPTZ DEFAULT NOW() );
About this issue
- Original URL
- State: closed
- Created 9 years ago
- Reactions: 4
- Comments: 24 (10 by maintainers)
Commits related to this issue
- Reload app after_create https://github.com/rails/rails/issues/21627#issuecomment-142625429 — committed to safing/stamp.community by davegson 6 years ago
This is still happening in Rails 5.1.4 (in my case using
pgcrypto
’sgen_random_uuid()
as a default.Working around by manually
.pluck
ing the fields out in anafter_create
callback.for future reference for anyone who lands here the following works in Rails 5.2.1, although I’m not sure when it was introduced
↑↑↑ I opened a new issue for the bug. So this issue - initially a feature request for functional defaults - can rightfully slumber and stay closed 😉
Ah, right @davegson , I missed that, cause I found this issue googling for the current problem. It would be awesome if you created another ticket!
Here’s my current work-around – rather than an after_create hook to reload, I override the particular attribute method to pluck from the db on first access – only if it’s nil and the model is already persisted. That way the extra fetch is only made if a caller asks for the value. And I call the (public? private? Not sure) rails method
clear_attribute_change
to set the db value without rails thinking it’s dirty and needs to be saved.@jrochkind my callback simply is:
after_create :reload
- dirty quick fix.I’m not sure if this belongs here or in a new issue. The original issue described how a postgres function by default would not be supported. Such as:
Now it does support this, but not in a clean way: you have to reload newly created AR objects to retrieve the values generated by postgres. This blog post describes the issue wonderfully.
I feel we should open up another Issue describing this problem and only reference to this closed one. If others agree I will do so later today.
The issue still remains in Rails 5.2.1. You need to set an
after_create
callback to work around it.I assume @asmega was referencing the feature that you could set functional defaults which is supported since Rails 5 and was already mentioned by @yawboakye
Still seeing this in ActiveRecord 5.1.3 with Rails 5.1.3/Ruby 2.4.1 and Postgres 9.6.4 The object field that has an enum type with a default String value gets set The object fields that have uuid_generate_v4() as a default value do get set in the database when the object is saved but not on the object itself (the id does get set though) . A call to object.reload does populate the fields.