4

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 :))

Mr.P
  • 1,182
  • 3
  • 20
  • 44
  • ok, I reported it to the pgsql team ... will keep you updated here guys ... really seems like a bug – Mr.P Aug 18 '21 at 15:07
  • 2
    To me it looks this was patched back in November of last year [Identity patch](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=17958972fe3bb03454a4b53756b29d65dc285efa). Hmm, I only see it in the version 14 notes [One page notes](https://bucardo.org/postgres_all_versions.html). – Adrian Klaver Aug 18 '21 at 15:20
  • @AdrianKlaver nice one ... will look into upgrading to some newer version ... – Mr.P Aug 18 '21 at 15:27
  • Except as far as I can tell it only appears in version 14 which is still in beta. – Adrian Klaver Aug 18 '21 at 15:30
  • I just tried on another DB (PostgreSQL 12.8 (Ubuntu 12.8-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit) and it doesn't work there either – Mr.P Aug 18 '21 at 15:35
  • That is what I'm saying, it does not appear to be in any of the current production releases including the ones just released August 12th. – Adrian Klaver Aug 18 '21 at 15:39

2 Answers2

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');
Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • 4
    The question is: why is this necessary when inserting two rows, but not when inserting one row. –  Aug 18 '21 at 14:28
  • `For an identity column defined as GENERATED ALWAYS, it is an error to insert an explicit value (other than DEFAULT) without specifying either OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE. (For an identity column defined as GENERATED BY DEFAULT, OVERRIDING SYSTEM VALUE is the normal behavior and specifying it does nothing, but PostgreSQL allows it as an extension.)` quote from the documentation – Mr.P Aug 18 '21 at 14:31
  • 1
    @Mr.P seems like that `other than DEFAULT` is incorrect. You should send it to their bugs mailing list. They will probably decide it's easier to remove it from the documentation and keep the behavior. – coladict Aug 18 '21 at 14:56
  • Allowing default here is specified in the SQL standard, so the documentation is correct, and this is a bug in the implementation. – Mark Rotteveel Aug 18 '21 at 15:04
  • @coladict: I don't think it's a bug in PostgreSQL, it works exactly the same in DB2 and SAP HANA. – Frank Heikens Aug 18 '21 at 15:07
  • well it worked before and it doesn't work now ... and logically I can't say it's a feature :) doesn't make any sense – Mr.P Aug 18 '21 at 15:09
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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263