Postgresql sequence and migrations

Rails
1

I have a specific need to ‘fixate’ my ‘roles’ table sequence when
migrating.

at migration #4, I have 28 records in my roles tables and I can manually
reset the roles_id_seq but it stands to reason that since I am doing
everything else in the migration, I might as well reset the sequence
there too.

so I tried this in my migration file…

execute 'SELECT setval ( "roles_id_seq", 0 );'

and it crashed with…

– execute(“SELECT setval ( “roles_id_seq”, 0 );”)
rake aborted!
PGError: ERROR: column “roles_id_seq” does not exist
: SELECT setval ( “roles_id_seq”, 0 );

How do I accomplish this inside of a migration?

Craig

2

At a guess, what you need to do is (assuming you’ve got a controller
named Foo) something like:

Foo.find_by_sql(‘SELECT setval (“roles_id_seq”, 0);’)

Note that find_by_sql lets you execute arbitrary SQL statements.

Regards

Dave M.

3

seemed possible but the result is still the same…

PGError: ERROR: column “roles_id_seq” does not exist
: SELECT setval ( “roles_id_seq”, 29 );

and of course there is no column ‘roles_id_seq’ as it is the postgresql
sequence for automatically numbering additions to table.

Is there a way to set this within a migration statement?

Craig

4

Craig W. wrote:

seemed possible but the result is still the same…

PGError: ERROR: column “roles_id_seq” does not exist
: SELECT setval ( “roles_id_seq”, 29 );

and of course there is no column ‘roles_id_seq’ as it is the postgresql
sequence for automatically numbering additions to table.

Is there a way to set this within a migration statement?

Craig

Which version of PGSQL are you using? You might want to try
variations of
SELECT setval(‘roles_id_seq’::text, 29)

I know the documentation always uses single quotes not double, and older
versions liked the explicit cast to text, newer versions (8.1.x) should
work without the case, I think.

Eric

5

Posting solution for anyone that follows in my footsteps…this was big.

execute 'ALTER SEQUENCE roles_id_seq RESTART WITH 29;'

migration statement for postgresql to re-set sequence

Craig

6

On Thu, 2006-06-15 at 17:58 +0200, Eric D. Nielsen wrote:

Craig

Which version of PGSQL are you using? You might want to try
variations of
SELECT setval(‘roles_id_seq’::text, 29)

I know the documentation always uses single quotes not double, and older
versions liked the explicit cast to text, newer versions (8.1.x) should
work without the case, I think.

I’m on RHEL so it’s PostgreSQL 7.4.8-1 (yeah, I know I could upgrade and
maybe will upgrade for autovacuum and stuff but production usage is
light and development usage is heavy)

It actually works as stated and that makes me happy…I probably need to
update my PostgreSQL book though because I think it pre-dates 7.4 and I
think it set me off with the wrong terminology…it wasn’t until I
examined the terminology used by pgadmin3 that I found a better way.

Thanks

Craig

7

There is a method in the Postgresql adapter called reset_pk_sequence. I
haven’t used it yet but it sounds like this is what you are looking for.

8

Lance wrote in post #93083:

There is a method in the Postgresql adapter called reset_pk_sequence. I
haven’t used it yet but it sounds like this is what you are looking for.

I used it:

Account.delete_all
Account.reset_pk_sequence

but, it didn’t work. The next Account.save had the :id = last :id + 1

At least it didn’t crash :slight_smile:

I’m still looking for a solution.

9

Bob G. wrote in post #1092090:

Lance wrote in post #93083:

There is a method in the Postgresql adapter called reset_pk_sequence. I
haven’t used it yet but it sounds like this is what you are looking for.

I used it:

Account.delete_all
Account.reset_pk_sequence

but, it didn’t work. The next Account.save had the :id = last :id + 1

At least it didn’t crash :slight_smile:

I’m still looking for a solution.

Here is the solution:

Account.delete_all
Account.connection.execute “ALTER SEQUENCE accounts_id_seq RESTART WITH
1”

Hope this helps

Bob G