I demonstrate the issue with the following simple table. (The actual table and JSON document have a lot more fields.)
CREATE table contact (
id bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
firstname VARCHAR,
lastname VARCHAR,
birthday timestamp with time zone NOT NULL
);
I am trying to insert into this table from a JSONB using jsonb_populate_record
:
INSERT INTO contact (firstname, lastname, birthday)
SELECT (jsonb_populate_record(NULL::contact,
'{
"firstname": "John",
"lastname": "Doe",
"birthday": "2023-09-28"
}'
)).*;
This fails with error:
ERROR: INSERT has more expressions than target columns
LINE 1: ...O contact (firstname, lastname, birthday) SELECT jsonb_popu...
I understand that the error is coming from the JSONB not containing the id
key and value. So, the jsonb_populate_record
function is creating a record with the id
column but since it's not specified in the INSERT
, it fails.
I have tried adding id
into the INSERT INTO contact (id, ....
but that fails with error that the id
is null (because my JSONB doesn't have any id
in it).
How can I make it work so that the id
is GENERATED BY DEFAULT AS IDENTITY
as specified in the table?
EDIT:
I was able to find a solution:
INSERT INTO contact (id, firstname, lastname, birthday)
SELECT (jsonb_populate_record(NULL::contact, jsonb_set('{
"firstname": "John",
"lastname": "Doe",
"birthday": "2023-09-28"
}', '{id}', to_jsonb(nextval(pg_get_serial_sequence('contact', 'id')))))).*;
However, the id
generated after this seems to be skipping 4 digits:
select * from contact;
id | firstname | lastname | birthday
----+-----------+----------+------------------------
10 | John | Doe | 2023-09-28 00:00:00-04
14 | John | Doe | 2023-09-28 00:00:00-04
18 | John | Doe | 2023-09-28 00:00:00-04
22 | John | Doe | 2023-09-28 00:00:00-04
Any idea why and how to avoid this?
There is another solution, one that doesn't touch the sequence by itself. Just create a custom data type, without the id and use this type:
CREATE TYPE CONTACT_BASE AS
(
firstname VARCHAR,
lastname VARCHAR,
birthday TIMESTAMP
);
INSERT INTO contact (firstname, lastname, birthday)
SELECT (JSONB_POPULATE_RECORD(
NULL::CONTACT_BASE -- this one
, '{
"firstname": "John",
"lastname": "Doe",
"birthday": "2023-09-28"
}'
)).*;
By the way, why do use a timestamp for the date of birth?