Debian Question posted in
The oficial documentation can be found here.

Insert multiple fields with identity column doesn't work – Debian

I might be overworked today, but I am not getting this situation. It has to be something silly that I am simply overlooking.

The table structure is as follows:

CREATE TABLE sample_table (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    name varchar(255) NOT NULL,
    description text NOT NULL,
    CONSTRAINT sample_table_pk PRIMARY KEY (id)
);

When I try to insert a single value, it works OK:

INSERT INTO sample_table (id, name, description)
VALUES (DEFAULT, 'John Doe', 'Test description');

However, when inserting multiple values, it fails:

INSERT INTO sample_table (id, name, description)
VALUES (DEFAULT, 'John Doe', 'Test description')
, (DEFAULT, 'Jane Eod', 'Not working');

Why? If I omit the DEFAULT value and PK (=id), it works great.

INSERT INTO sample_table (name, description)
VALUES ('John Doe', 'Test description')
, ('Jane Eod', 'Not working');

Why DEFAULT is not working for batch insert?

PSQL version: PostgreSQL 11.13 (Debian 11.13-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

UPDATE:

it was accepted by the PGSQL team as a bug a is fixed in v14 .. hopefully, they will propagate it to all other stable versions .. there is a stormy discussion about that :))

Tags:

2

Answers


  1. 0

    You need OVERRIDING SYSTEM VALUE:

    INSERT INTO sample_table (id, name, description) 
OVERRIDING SYSTEM VALUE
VALUES (DEFAULT, 'John Doe', 'Test description')
, (DEFAULT, 'Jane Eod', 'Now working fine');
    Login or Signup to reply.
  2. 0

    This is a known PostgreSQL bug that has been fixed in v14 (kudos to Adrian Klaver who found that).

    The bug fix did not get backported to older releases, because it is rather invasive, and PostgreSQL tries to avoid backporting such patches to avoid introducing new bugs with minor releases; see Tom Lane’s answer to your bug report:

    Yeah, per spec you shouldn’t have to say OVERRIDING SYSTEM VALUE
    for this case, but it didn’t seem worth the risk of back-patching
    to improve that in stable branches.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search