Default a Postgres column to the current date in a Rails migration

If you want to have a Postgres column (aside from created_at) that you want to be populated with the current date if no other date is specified, you may be tempted to create a migration like this:

add_column :invoices, :paid_on, :date, default: 'now()'

That will look like it works – you create a record, it gets populated with today’s date, and all is good. However, if you look at your schema, you will notice that new field has a default of today’s date instead of now(). Oops. :)

You might try to create the column with the recommendation from the Postgres documentation:

add_column :invoices, :paid_on, :date, default: 'CURRENT_DATE'

But that fails because Rails tries to quote that ‘CURRENT_DATE’ for you before it goes to Postgres, which blows up. Now what?

Here’s how to do what you want:

add_column :invoices, :paid_on, :date, default: { expr: "('now'::text)::date" }

This avoids the quoting problem (by using expr) and avoids the always-insert-migration-date’s-date problem (by using the default function of (‘now’::text)::date, which is effectively the same as CURRENT_DATE.

And now when you insert a record without specifying a value for that field, you get the date of the insertion, rather than the date of the field being created. :)

Comments