如何在使用序列 ID 时使用 INSERT ... ON CONFLICT (id) DO UPDATE ... 语法？

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时，序列（数据库的自动增量）将不再使用。

如何使用INSERT ... ON CONFLICT (id) DO UPDATE...语法与序列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 *;

它抛出了一个错误:

错误:列“tbltest_ID”中的空值违反了非空约束

感谢您的时间。

- user2109254

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 *;

希望这能帮助到某些人；-)

- user2109254