Using postgres sequences on id columns


#1

I just switched from using a column type of SERIAL to trying to use a
Postgres Sequence defined as:

CREATE SEQUENCE unique_sequence START 101;

The column definition went from “id SERIAL NOT NULL” to

"id INTEGER NOT NULL DEFAULT nextval(‘unique_sequence’), "

When i insert outside of rails, everything works fine. Inside rails,
however, I get this error message:

ERROR C42P01 Mrelation “project_id_seq” does not
exist Fnamespace.c L201 RRangeVarGetRelid

My question is: Do you have to use Serial types on id columns? I would
prefer to use a sequence so i can ensure that the ids of rows in
several tables are entirely unique.

thanks.


#2

SERIAL is just an alias for ‘integer no null default
nextval(‘sequence’)…’ Looks like Rails is looking
for a sequence named project_id_seq yet you created
one named unique_sequence.

CSN

— Larry W. removed_email_address@domain.invalid wrote:

nextval(‘unique_sequence’), "
prefer to use a sequence so i can ensure that the
ids of rows in
several tables are entirely unique.

thanks.


Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails


Yahoo! FareChase: Search multiple travel sites in one click.


#3

Blair Z. wrote:

Right, you need the standard sequence name of
“#{table_name}_#{primary_key_column_name}_seq”, unless you use AR’s
set_sequence_name method to name the sequence.

But you will still run into a problem with these explicitly named
sequences in Postgresql that RoR 0.14.2 doesn’t handle:

http://dev.rubyonrails.com/ticket/2594

Well I have a standard name for my sequence but it doesn’t work either.

?> Player.primary_key => “id”

Player.connection.default_sequence_name “players” =>
“players_id_seq” >> Player.connection.pk_and_sequence_for “players” =>
nil >>

table definition:

                                  Table "public.players"
   Column      |          Type          | 

Modifiers
------------------±-----------------------±---------------------------------------------------
id | integer | not null default
nextval(’“players_id_seq”’::text)
positie | integer |
active | boolean | not null default false
perm_level | smallint | not null default 1
email | character varying(150) | not null
fname | character varying(60) | not null
lname | character varying(80) | not null

Jeroen


#4

Right, you need the standard sequence name of
“#{table_name}_#{primary_key_column_name}_seq”, unless you use AR’s
set_sequence_name method to name the sequence.

But you will still run into a problem with these explicitly named
sequences in
Postgresql that RoR 0.14.2 doesn’t handle:

http://dev.rubyonrails.com/ticket/2594

Regards,
Blair