PostgreSQL: how to create both sequence and table simultaneously?

Question

I need to create a table with a sequence associated to the gid column that would start from a given value, say 300. I'd like to know if there is a shorter way to achieve that:

CREATE TABLE mytable (gid INTEGER NOT NULL, size INTEGER);

CREATE SEQUENCE mytable_gid_seq MINVALUE 300;

ALTER TABLE mytable ALTER COLUMN gid SET DEFAULT nextval('mytable_gid_seq'::regclass);

Thanks for help!

Answer 1

You can create the sequence implicitely through the serial (pseudo) type and then change its current value:

CREATE TABLE mytable (gid serial NOT NULL, size INTEGER); 
select setval('mytable_gid_seq', 100);

or alternatively using

CREATE TABLE mytable (gid serial NOT NULL, size INTEGER); 
alter sequence mytable_gid_seq restart with 100;

This is slightly different than defining a minvalue - but that would only matter if the sequence was set to wrap. This version also creates a link between the sequence and the table, so that the sequence is automatically dropped when the table is dropped (in your script you would need to alter sequence mytable_gid_seq owner to mytable.gid);