postgres doesn't autogenerate PK if the PK is inserted manually-CodePudding

I have a simple table like this:

CREATE TABLE IF NOT EXISTS myval
(
    id integer NOT NULL DEFAULT nextval('myval_myval_id_seq'::regclass),
    name character varying(255),
    CONSTRAINT "PK_aa671c3359a0359082a84ecb801" PRIMARY KEY (id)
)

the sequence definition is:

CREATE SEQUENCE IF NOT EXISTS myval_myval_id_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 2147483647
    CACHE 1
    OWNED BY myval.myval_id;

when I insert data along with the primary key:

INSERT INTO myval(id, name) VALUES (1, 'sdf');
INSERT INTO myval(id, name) VALUES (2, 'sdf');
INSERT INTO myval(id, name) VALUES (3, 'sdf');
INSERT INTO myval(id, name) VALUES (4, 'sdf');

then, I insert it without the PK:

INSERT INTO myval(name) VALUES ('new sdf');

it gives an error saying:

duplicate key value violates unique constraint "PK_aa671c3359a0359082a84ecb801", DETAIL: Key (myval_id)=(1) already exists.

I expected it to start with PK value of 5 but, instead it gives an error. Can we configure postgres to skip conflicting values and generate from the closest available value to use instead of throwing an error?

CodePudding user response：

The best way to avoid such conflicts is to use identity columns - in this case a GENERATED ALWAYS AS IDENTITY seems the right option.

CREATE TABLE IF NOT EXISTS myval
(
    id integer GENERATED ALWAYS AS IDENTITY,
    name character varying(255),
    CONSTRAINT "PK_aa671c3359a0359082a84ecb801" PRIMARY KEY (id)
);

This will work like a sequence (serial), however it will fail if the user tries to manually insert a value in this column

INSERT INTO myval (id,name) 
VALUES (1,'foor');

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
TIP:  Use OVERRIDING SYSTEM VALUE to override.

If for whatever reason you must override this behavior in a certain INSERT statement you can do so using OVERRIDING SYSTEM VALUE, as the error message above suggests

INSERT INTO myval (id,name) OVERRIDING SYSTEM VALUE
VALUES (1,'foo');

You might be able to achieve a sequential value using serial even if the user screws things up with inserts, e.g. using trigger functions. But such an architecture is hard to maintain and imho is definitely not worth the trouble.

Demo: db<>fiddle