Search code examples
sqlpostgresqlconstraintsauto-increment

How do I enforce an auto increment in postgres?

I can create an auto increment by setting a sequence and setting a tables column default value to be that sequence, but I can still manually enter a value that is not that sequence. How can I enforce a constraint to this on insert? Is using a trigger the only way?

Solution

  • Does this help?

    Ive tested this and you can insert the primary key as long as its UNIQUE. If its Null the auto sequence kicks in (tablename_colname_seq) and inserts a UNIQUE primary key.

    -- Table: test
CREATE SEQUENCE tablename_colname_seq;


CREATE TABLE foo( primary_id int not null UNIQUE default  nextval('tablename_colname_seq'),

a_nother int ) 
WITH ( OIDS=FALSE );

    ALTER TABLE foo OWNER TO postgres;

    -- Test Data

    insert into foo (primary_id) values(14)
insert into foo (a_nother) values(13);
select * from foo;

    All the best