How to link a sequence to a field with PostgreSQL?

Question

In documentation of postgreSQL 9.6, it said to have serial equivalent we have to link the sequence to the field.

https://www.postgresql.org/docs/9.6/datatype-numeric.html (¤ 8.1.4)

I exactly applied the same code :

CREATE SEQUENCE seq_import_trame INCREMENT 1 START 1; 

CREATE TABLE import_trame (
    id integer NOT NULL DEFAULT nextval('seq_import_trame'), 
    ...,
    CONSTRAINT pk_import_trame PRIMARY KEY (id)
);
ALTER SEQUENCE seq_import_trame OWNED BY import_trame.id;

But I have an error while executing script with pgAdmin.

Relation "seq_import_trame" already exist".
Code: 42P07
Line 8: ALTER SEQUENCE.....

It's very strange because 42P07 error is duplicate_table. And if we don't make the link, the sequence is not dropped when we drop the table.

Any idea ?

Apparently the sequence already exists, so you can remove the create sequence part — user330315
– user330315, Commented Feb 7, 2019 at 10:52
Your code works fine. You are getting an error on the first line in the script, that is all. Here is a db<>fiddle: dbfiddle.uk/…. — Gordon Linoff
– Gordon Linoff, Commented Feb 7, 2019 at 12:18
It's very strange....in pgAdmin there are 2 mode for execution: 1) execute 2) execute pgscript. With pgscript it's working because probably it commit each command line by line. In normal execution, still an error on ALTER SEQUENCE. I can't remove the first line as u suggest. Sequence doesnt exist before, and its necessary to create it with particular name (too bad my client require this convention name). — MychaL
– MychaL, Commented Feb 7, 2019 at 12:36
The sequence already exists, use CREATE SEQUENCE IF NOT EXISTS .... — Ondřej Xicht Světlík
– Ondřej Xicht Světlík, Commented Feb 7, 2019 at 16:48

Alex Yu · Accepted Answer · 2019-02-07 13:25:55Z

2

I see no problem on DB Fiddle:

Schema (PostgreSQL v9.6)

CREATE SEQUENCE seq_import_trame INCREMENT 1 START 1; 

CREATE TABLE import_trame (
    id integer NOT NULL DEFAULT nextval('seq_import_trame'), 
    CONSTRAINT pk_import_trame PRIMARY KEY (id)
);
ALTER SEQUENCE seq_import_trame OWNED BY import_trame.id;

View on DB Fiddle

Check again your script.

answered Feb 7, 2019 at 13:25

Alex Yu

3,5771 gold badge27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MychaL Over a year ago

Seems a pgAdmin problem. On SQL execution it returns an error on ALTER SEQUENCE line. With pgScript execution it's OK. Strange.

MychaL Over a year ago

Not Really, but I will solve the topic cause it’s not a PostgreSQL pb...it’s pgAdmin :(

Collectives™ on Stack Overflow

How to link a sequence to a field with PostgreSQL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related