sequences and Postgresql

[prev] [thread] [next] [Date index for 2004/07/07]

If one has a table with a primary key which is a 'serial', i.e. an
integer/bigint whose default value is the next value in a sequence, the
documentation for CDBI says that you should not try to use the
__PACKAGE__->sequence() for that column. If you do, then _prepopulate_id
will make an invalid SQL request.

Assume that we have column "category_id" in some table named "categories".
Here is the SQL to create the table with only the column in question:

	CREATE TABLE "categories" (
		"category_id" SERIAL PRIMARY KEY
	);
	

This results in the creation of a new relation (table) called
"categories_category_id_seq". 

_prepopulate_id ends up generating SELECT NEXTVAL('category_id')
but this is wrong. The correct SQL is SELECT 
NEXTVAL('categoires_category_id_seq');
Of course the optimal performance is to leave this column out entirely and
let Postgresql take care of it.

At this time I do not see how to achieve either approach. If you leave out 
the primary key, CDBI will stick it in automatically on a create thereby 
causing the 
failure.