postgresql - INSERT ... ON CONFLICT (id) DO UPDATE... 语法如何与序列 ID 一起使用？

Question

在 postgresql 9.5 中，INSERT ... ON CONFLICT (id) DO UPDATE... 语法如何与序列 ID 一起使用？

在具有以下列的表 tbltest 中:

• tbltest_ID
• tbltest_Name
• tbltest_Description

tbltest_ID 在数据库中有一个自动递增的序列。

以下适用于更新，例如；更新 ID 为 4 的记录:

INSERT INTO tbltest (
    tbltest_ID,
    tbltest_Name,
    tbltest_Description) 
VALUES 
(4, 'test name','test description')
ON CONFLICT (tbltest_ID) DO UPDATE SET (
    tbltest_Name,
    tbltest_Description) = (
    excluded.tbltest_Name,
    excluded.tbltest_Description) RETURNING *;

但为了让数据库为插入创建序列 ID，我需要从语句中删除 ID 列:

INSERT INTO tbltest (
    tbltest_Name,
    tbltest_Description) 
VALUES 
('test name','test description')
ON CONFLICT (tbltest_ID) DO UPDATE SET (
    tbltest_Name,
    tbltest_Description) = (
    excluded.tbltest_Name,
    excluded.tbltest_Description) RETURNING *;

如果我想更新多个记录，一些是新的，一些是现有的，这就会成为一个问题。就好像我删除了 ID 列一样，它们都是插入的，如果我把它留在那里，我必须在 VALUES 数组中为每一行提供一个 ID 值，当我定义一个 ID 时，序列(db 的自动递增) 不再使用。

INSERT ... ON CONFLICT (id) DO UPDATE... 语法 spost 如何与序列 ID 一起使用以插入/更新将包含新记录和现有记录的记录集？

例如，以下内容不起作用:

INSERT INTO tbltest (
    tbltest_ID,
    tbltest_Name,
    tbltest_Description) 
VALUES 
(NULL, 'new record','new record description'),
(4, 'existing record name','existing record description')
ON CONFLICT (tbltest_ID) DO UPDATE SET (
    tbltest_Name,
    tbltest_Description) = (
    excluded.tbltest_Name,
    excluded.tbltest_Description) RETURNING *;

它抛出一个错误:

ERROR: null value in column "tbltest_ID" violates not-null constraint

感谢您的宝贵时间。

Answer 1

好的，刚刚解决了。我读了 Neil Conway 的这篇很棒的文章: http://www.neilconway.org/docs/sequences/

他展示了使用 DEFAULT 关键字告诉数据库使用列的序列值。

所以这是现在可以使用的更新示例:

INSERT INTO tbltest (
    tbltest_ID,
    tbltest_Name,
    tbltest_Description) 
VALUES 
(DEFAULT, 'new record','new record description'),
(4, 'existing record name','existing record description')
ON CONFLICT (tbltest_ID) DO UPDATE SET (
    tbltest_Name,
    tbltest_Description) = (
    excluded.tbltest_Name,
    excluded.tbltest_Description) RETURNING *;

希望这对某人有帮助 ;-)