primary key sequence does not exist

Question

I have a postgresql database and after migrating to a newer version and importing my old data I have a problem with the primary key:

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "browser_link_pkey" Detail: Key (id)=(173) already exists.

So I wanted to reset my sequence, but running:

select nextval('browser_link_id_seq')

Also fails with:

column „browser_link_id_seq“ does not exist SQL Status:42703

This is the SQL for table creation

CREATE TABLE browser_link ( id bigint NOT NULL, .... ); ALTER TABLE ONLY browser_link ADD CONSTRAINT browser_link_pkey PRIMARY KEY (id);

I tried selecting the serial sequence, but it seems none exists:

postgres=# \connect historify You are now connected to database "historify" as user "postgres". historify=# select pg_get_serial_sequence('browser_link', 'id'); pg_get_serial_sequence ------------------------ (1 row)

I am using postgresql 9.5.3. Also, until the error occured the id column did increment as expected, so somehow it does work.

Now my two questions:

why does the autoincrement work?
how do I reset the autoincrement counter?

You need single quotes in select nextval('browser_link_id_seq') Double quotes are for identifiers. (the sequence name in the function call may look like an identifier, but it is actually a string literal) — joop
– joop, Commented Jul 1, 2016 at 9:27
I updated the question, I tried both "" and '', neither works. — sveri
– sveri, Commented Jul 1, 2016 at 9:46
You had more than one error. The other error is the sequence is not connected to the id column. You should alter the column, adding the sequence as a default value/expression. — joop
– joop, Commented Jul 1, 2016 at 9:56
I still dont understand that, if the sequence (which one exactly?) is not connected to the column, then how does the column autoincrement already? I never created a sequence explicitly? My assumption is that the primary key constraint does create a sequence. — sveri
– sveri, Commented Jul 1, 2016 at 10:01

joop · Accepted Answer · 2016-07-01 12:06:03Z

Serials in Postgres are integers with a default supplied by a sequence. In the following fragment, table "one" and table "two" are exactly equivalent:

CREATE TABLE one ( id bigserial NOT NULL PRIMARY KEY , name varchar ); CREATE TABLE two ( id bigint NOT NULL PRIMARY KEY , name varchar ); CREATE SEQUENCE "two_id_seq" INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1; ALTER SEQUENCE "two_id_seq" OWNED BY two.id ; ALTER TABLE two ALTER COLUMN id SET default nextval('two_id_seq') ; \d one \d two

As you can see, the serial syntax for the one table is a lot more compact (it is actually a shorthand notation for the 4 statements needed for the two table)

So, in your problemtable, you could either rebuild the table using bigserial instead of bigint as a data type, or "attach" the existing serial to the existing id column via the ALTER table ... ALTER COLUMN ... syntax.

You can set the sequence to a new value by 'setval('name', val);` example:

INSERT INTO two(id, name) VALUES ( 13, 'thirteen' ); select nextval('two_id_seq'); SELECT setval ('two_id_seq', (select max(id) FROM two)); select * from two; select nextval('two_id_seq');

Result:

INSERT 0 1 nextval --------- 1 (1 row) setval -------- 13 (1 row) id | name ----+---------- 13 | thirteen (1 row) nextval --------- 14 (1 row)

Thanks, I will do that and recreate the table. Still I do not understand why it works already without a sequence.

Collectives™ on Stack Overflow

primary key sequence does not exist

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related