INSERT ... ON CONFLICT（id）UPDATE ...语法如何与序列ID一起使用？ [英] How can the INSERT ... ON CONFLICT (id) DO UPDATE... syntax be used with a sequence ID?

问题描述

在PostgreSQL 9.5中，如何将INSERT ... ON CONFLICT（id）UPDATE ...语法与序列ID一起使用？

In postgresql 9.5 how can the INSERT ... ON CONFLICT (id) DO UPDATE... syntax be used with a sequence ID?

在表中 tbltest 包含以下列：

• tbltest_ID


• tbltest_Name


• tbltest_Description

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

Where tbltest_ID has a sequence in the db that does auto increment.

下面的代码可以很好地进行更新，例如；更新ID为4的记录：

The following works fine for updates, eg; updating the record with an ID of 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列从语句中：

But in order to get the DB to create the sequence ID for inserts I need to remove the ID column from the statement:

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的自动递增

This becomes a problem if I want to update multiple records some new and some existing. As if I remove the ID column, they all be come inserts, and if I leave it in there, I have to provide an ID value in the VALUES array for each row, and when I define an ID, the sequence (db's auto increment) is no longer used.

INSERT ... ON CONFLICT（id）DO UPDATE ...语法spost与序列ID一起使用来插入/更新包含新记录和现有记录的记录集？

How is the INSERT ... ON CONFLICT (id) DO UPDATE... syntax spost to be used with a sequence ID to insert/update an set of records that will contain both new records and existing records?

例如，以下操作不起作用：

For example, the following does not work:

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列中的空值违反了非空约束

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

感谢您的时间。

推荐答案

确定就可以了。我从尼尔·康威（Neil Conway）读了这篇很棒的文章：
http://www.neilconway.org/ docs / sequences /

Ok just worked it out. I read this great article from Neil Conway: http://www.neilconway.org/docs/sequences/

其中他显示了使用 DEFAULT 关键字告诉数据库执行以下操作

Where he shows the use of the DEFAULT keyword to tell the DB to use the sequence value for the column.

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

So here is the updated example that now works:

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

希望这对某人有所帮助；-）

Hope this helps someone ;-)

这篇关于INSERT ... ON CONFLICT（id）UPDATE ...语法如何与序列ID一起使用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！