In a Rails migraiton, how do I set the default value of a column to be NOW() instead of the time when I ran the migration?

Question

I have a PostGres 9.4 database. I want to change the default column type of a DATETIME column to be the time when the record was created. I thought this was the right way, in as far as this is my rails migration

class ChangeDefaultValueForStratumWorkerSubmissions < ActiveRecord::Migration[5.1]
  def change
    change_column_default(:stratum_worker_submissions, :created_at, 'NOW')
  end
end

but when I look at my database, the default timestamp shows as the time when I ran the migration, instead of the expression I want. How do I write a migration that will do what I want?

      Column       |            Type             |                                 Modifiers
-------------------+-----------------------------+----------------------------------------------------------------------------
 id                | integer                     | not null default nextval('stratum_worker_submissions_id_seq'::regclass)
 stratum_worker_id | integer                     |
 created_at        | timestamp without time zone | not null default '2018-04-04 19:46:22.781613'::timestamp without time zone

Answer 1

It isn't well documented but you can supply a lambda as the default value in a migration and that will do The Right Thing. If you say this:

def change
  change_column_default :stratum_worker_submissions, :created_at, -> { 'now()' }
end

then the column's default value will be set to now() and the database function now() won't be called until a default value is needed for the column. Then if you \d stratum_worker_submissions in psql you'll see:

created_at | timestamp without time zone | not null default now()

as desired. Any other default will be evaluated when the migration runs and you'll end up with a fixed timestamp as the default.

---

Alternatively, you can always do it by hand using SQL:

def up
  connection.execute(%q(
    alter table stratum_worker_submissions
    alter column created_at
    set default now()
  ))
end
def down
  connection.execute(%q(
    alter table stratum_worker_submissions
    alter column created_at
    drop default
  ))
end

Note that if you start manually changing the schema with SQL you might start doing things that won't appear in db/schema.rb as you can quickly get into SQL that ActiveRecord doesn't understand. If that happens then you can change from db/schema.rb to db/structure.sql by changing config/application.rb:

config.active_record.schema_format = :sql

and then replacing db/schema.rb with db/structure.sql in revision control and using the db:structure rake tasks in place of the usual db:schema tasks.