CODEWITHSUNDEEP
sqlruby-on-railspostgresqlmigration
tsdbrown
tsdbrown

Reputation: 5058

How can I set the starting point for the primary key (ID) column in Postgres via a rails migration

I am deploying a rails app to heroku which uses PostgreSQL as its back-end. In my database migration I normally set the ID field for things likes reports etc to at least 1000, most clients don't seem to like starting at 1.

Normally I use mysql and I simply add an sql specific after my table creation:

def self.up
    create_table :reports do |t|
       t.references :something
       ...
    end
    execute("ALTER TABLE reports AUTO_INCREMENT = 1000;")
end

Does anybody know how I can acheive the same for PostgreSQL, ideally I would like the migration to build the table itself so that's not DB specific.

I guess a silly way of achieving my goal would be to create and delete 999 records in a loop, ouch.

Upvotes: 13

Views: 12842

Answers (2)

Maxim Sloyko
Maxim Sloyko

Reputation: 15866

In postgres, like in many other databases, auto increment feature is done via Sequences. For every Serial and the likes fields sequences are created automatically by Postres for you and named something like TABLENAME _ COLUMNNAME _ seq.

So, you have to just alter the corresponding sequence, like this:

ALTER SEQUENCE example_id_seq RESTART 1000 -- corrected from START

Upvotes: 8

Michael Krelin - hacker
Michael Krelin - hacker

Reputation: 143071

Have no idea about rubies and railroads part, but query you're talking about is

ALTER SEQUENCE reports_something_seq RESTART 1000;

You will have to look up your table for the sequence name and postgresql documentation for general education regarding the matter ;-)

Upvotes: 19

Related Questions