2

From this: How to make a primary key start from 1000? It seems that I need to issue the SQL command: ALTER TABLE tbl AUTO_INCREMENT = 1000; But I only have been dealing with the database through the ActiveRecord abstraction. Is there a way to achieve it via active record? Either at the time of migration or on the fly when creating new record on the database?

I have tried both the following and failed:

@record= myRecord.new
while @record.id < 1000 do
    @record= myRecord.new
end

It is inefficient but it would just happen once, yet Rails report that @record.id is nil so cannot do the < comparasion and so I try to save the @record first and then see what id (primary key) value it has been assigned by the database

@record.save
if @record.id <1000
    @record.id = 1000 + @record.id
    @record.save!             
end

Somehow rails reports back that one of the unique field in @record is already there so cannot save the @record again.

EDIT: My bad, the above is MySQL command... for PostgreSQL, it seems to be something along the line ( http://archives.postgresql.org/pgsql-general/2006-10/msg01417.php ):

 SELECT setval('id_seq',100111);

However, I tried to run it on the Ruby console of my deployment environment (shared database on Heroku) and I just got !Internal server error back :-( my ruby class is called:

class Mytable < ActiveRecord::Base

so I run this command:

Mytable.connection.execute('SELECT setval('id_seq', 1000)')

and got Internal server error (tried with both 'id' and 'id_seq' in the above command) But it may be some sort of Ruby on Heroku specific issue that is causing the trouble, so I would investigate and posts another question instead of changing this one. Thanks!

Addition related PostgreSQL command materials:

http://pointbeing.net/weblog/2008/03/mysql-versus-postgresql-adding-an-auto-increment-column-to-a-table.html

http://archives.postgresql.org/pgsql-general/2006-10/msg01417.php

How to reset postgres' primary key sequence when it falls out of sync?

Community
  • 1
  • 1
S.Y.Chan
  • 528
  • 5
  • 12

1 Answers1

4

You can execute raw sql like this:

ActiveRecord::Base.connection.execute('ALTER TABLE tbl AUTO_INCREMENT = 1000');

Or from any descendant of ActiveRecord::Base (i.e. any model class), like this:

MyRecord.connection.execute('ALTER TABLE tbl AUTO_INCREMENT = 1000')
Ben Lee
  • 52,489
  • 13
  • 125
  • 145
  • I'm pretty sure that in PostgreSQL this will fail as there is no auto_increment = in the alter table. What you need is to find the sequence being used and then issue a command like "select setval('seqname',1000);" – Scott Marlowe Sep 26 '11 at 21:36
  • @ScottMarlowe, you may well be right, I just took the OP's word on the required literal SQL since I am not familiar with PostgreSQL. If you are familiar with it, maybe you can post a better answer than mine? – Ben Lee Sep 26 '11 at 21:50
  • Thanks @ScottMarlowe ! I have also find the PostgreSQL command before I read your comment. I am using the default ActiveRecord auto generated Primary Key for the class, so it is just named "id", but I don't know the name of the sequence that is associated with its value generation. – S.Y.Chan Sep 27 '11 at 04:33
  • 1
    Ben Lee, your answer was essentially the right one, how to run raw sql. I just added the last bit. As for the sequence name, can you connect in any way to the db to run a \d tablename and see? If not, it usually goes by the standard naming convention of tablename_colname_seq. So something like select setval('table_col_seq',1000); – Scott Marlowe Sep 27 '11 at 11:26