1

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.

0
2

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.

2
  • 1
    This was helpful. Can this logic be executed using the change syntax available in recent version of rails? – Analytical Monk Apr 13 '17 at 10:07
  • change is shorthand for up/down ( change will attempt to automatically implement an opposing down method for you). Using execute prevents the shorthand. up and down are still available for just this reason. – Karew Apr 13 '17 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.