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
I updated the question, I tried both "" and '', neither works. — sveri
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
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

joop joop · Accepted Answer · 2016-07-01T10:25:02

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)

primary key sequence does not exist

1 Answers