Sql 如何使用jsonn_populate_record()插入到包含IDENTITY列的表中

我用下面的简单表格演示了这个问题.(实际的表和JSON文档有更多的字段.)

CREATE table contact (
    id bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    firstname VARCHAR,
    lastname VARCHAR,
    birthday timestamp with time zone NOT NULL
);

我正在try 使用jsonb_populate_record从JSONB插入到这个表中:

INSERT INTO contact (firstname, lastname, birthday)
SELECT (jsonb_populate_record(NULL::contact, 
'{
    "firstname": "John",
    "lastname": "Doe",
    "birthday": "2023-09-28"
}'
)).*;

此操作失败，并显示错误:

ERROR: INSERT has more expressions than target columns
LINE 1: ...O contact (firstname, lastname, birthday) SELECT jsonb_popu...

我知道错误来自不包含id键和值的JSONB.因此，jsonb_populate_record函数正在创建一条包含id列的记录，但由于INSERT中没有指定它，所以它失败了.

我try 将id添加到INSERT INTO contact (id, ....中，但失败了，错误为id为空(因为我的JSONB中没有任何id).

我怎么才能让它工作，使id是表中指定的GENERATED BY DEFAULT AS IDENTITY？

EDIT:个

我找到了一个解决方案:

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')))))).*;

然而，这之后生成的id似乎跳过了4位数:

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

你知道为什么和如何避免这种情况吗？