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

Most upvoted comments

This is still happening in Rails 5.1.4 (in my case using pgcrypto’s gen_random_uuid() as a default.

Working around by manually .plucking the fields out in an after_create callback.

ghost on Oct 28, 2017

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

def change
  enable_extension 'pgcrypto'

  add_column :admins, :api_key, :text, null: false, default: -> { "digest((gen_random_uuid())::text, 'sha256'::text)" }
end

asmega on Sep 27, 2018

↑↑↑ I opened a new issue for the bug. So this issue - initially a feature request for functional defaults - can rightfully slumber and stay closed 😉

davegson on Oct 17, 2018

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.

  # Due to rails bug, we don't immediately have the database-provided value after create. :(
  # If we ask for it and it's empty, go to the db to get it
  # https://github.com/rails/rails/issues/21627
  def friendlier_id(*_)
    in_memory = super

    if !in_memory && persisted? && !@friendlier_id_retrieved
      in_memory = self.class.where(id: id).limit(1).pluck(:friendlier_id).first
      write_attribute(:friendlier_id, in_memory)
      clear_attribute_change(:friendlier_id)
      # just to avoid doing it multiple times if it's still unset in db for some reason
      @friendlier_id_retrieved = true
    end

    in_memory
  end

jrochkind on Oct 16, 2018

@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:

add_column :whatever, :whatever, default: -> { "some_db_func()"}

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.

davegson on Oct 16, 2018

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

davegson on Oct 15, 2018

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.

AndyKriger on Aug 30, 2017