PostgreSQL Rails – Use ID Column as Default Value for Another Non-Nullable Column

postgresqlrails

Here's one example where such a requirement comes up:

Suppose we have a table 'sample' in our database which has a column 'col1' and an auto-incremental 'id' column. We want the default value of 'col1' to be taken as the value of the 'id' column. How do I do that?

I am using rails to write the migration script which will create the table in my postgres databse.

Best Answer

You will need to manually create your own sequence instead of letting Postgres/Rails do it for you. Then, because you know the name of the sequence you can use currval() to grab the current value of id as the DEFAULT value for other rows.

In a Rails migration, you can drop down to raw SQL for this:

class CreateSampleTable < ActiveRecord::Migration

  def up

    execute "CREATE SEQUENCE sample_id_seq"

    execute %{
      CREATE TABLE sample (
        id   bigint UNIQUE NOT NULL DEFAULT nextval('sample_id_seq'),
        col1 bigint        NOT NULL DEFAULT currval('sample_id_seq')
        -- Add other columns here
      )
    }

    execute "ALTER SEQUENCE sample_id_seq OWNED BY sample.id"

  end

  def down
    drop_table :sample
  end

end

Now whenever you insert a row into this table, id will auto-increment and col1 will have the same value as id by default.

Related Question