在Posgres 9.5中使用带有WITH聚类的upsert时，如何检索插入行的ID？ [英] How to Retrieve id of inserted row when using upsert with WITH cluase in Posgres 9.5?

问题描述

我正在尝试使用 WITH在Postgres 9.5中进行不正常查询

 ，其中s为（
从产品
中选择id 
，其中product_key ='test123'
），即为（
插入产品（product_key，count_parts）
选择'test123'，33 
不存在（从s中选择1）
返回id 
）
更新产品
 set product_key ='test123'，count_parts = 33 
其中id = （从s中选择id）
返回id 
  

显然，我只在以下位置获取ID更新，并且即使我知道插入成功也不会获得插入内容。

我需要以某种方式修改此查询，以便在插入时都能获得ID和更新。

谢谢！

解决方案

我不清楚您为什么在WITH First SELECT上执行操作，但是您只返回UPDATE <$的原因c $ c> id 是因为您没有选择INSERT返回。

正如注释中提到的（和链接的），Postgres 9.5支持INSERT。

以及9.5之前和之后的一些示例：

9.5之前的版本：使用WITH

的常用方法

 有AS（
更新产品
 SET product_key ='test123'，count_parts = 33 
 WHERE product_key ='test123'
返回ID 
），i AS（
插入
进入产品（ product_key，count_parts）
选择'test123'，33 
不存在（从u选择1）
返回ID 
）
选择* 
从（从u 
的SELECT ID从i 
的SELECT ID从i 
）r; 
  

9.5之后：使用INSERT .. ON CONFLICT

 插入产品（product_key，count_parts）
值（'test123'，33）
 CONFLICT（product_key）做
更新
 SET product_key ='test123'，count_parts = 33 
返回ID; 
  

更新：

正如评论中所暗示的，使用 INSERT .. ON CONFLICT 的方式可能会有轻微的弊端。

如果表使用自动递增功能并且此查询发生很多，那么 WITH 可能是一个更好的选择。

查看更多： https://stackoverflow.com/a/39000072/1161463

I'm trying to do upset query in Postgres 9.5 using "WITH"

with s as (
            select id
            from products
            where product_key = 'test123'
        ), i as (
            insert into products (product_key, count_parts)
            select 'test123', 33
            where not exists (select 1 from s)
            returning id                       
        )
        update products
        set product_key='test123', count_parts=33
        where id = (select id from s) 
        returning id

Apparently I'm retrieving the id only on the updates and get nothing on insertions even though I know insertions succeeded.

I need to modify this query in a way I'll be able the get the id both on insertions and updates.

Thanks!

解决方案

It wasn't clear to me why you do at WITH first SELECT, but the reason you get only returning UPDATE id is because you're not selecting INSERT return.

As mentioned (and linked) in comments, Postgres 9.5 supports INSERT ON CONFLICT Clause which is a much cleaner way to use.

And some examples of before and after 9.5:

Before 9.5: common way using WITH

WITH    u AS (
            UPDATE      products
            SET         product_key='test123', count_parts=33
            WHERE       product_key = 'test123'
            RETURNING   id
        ),i AS (
            INSERT
            INTO        products ( product_key, count_parts )
            SELECT      'test123', 33
            WHERE       NOT EXISTS( SELECT 1 FROM u )
            RETURNING   id
        )
SELECT  *
FROM    (       SELECT id FROM u
        UNION   SELECT id FROM i
        ) r;

After 9.5: using INSERT .. ON CONFLICT

INSERT INTO products ( product_key, count_parts )
VALUES      ( 'test123', 33 )
ON CONFLICT ( product_key ) DO
UPDATE
SET         product_key='test123', count_parts=33
RETURNING   id;

UPDATE:

As hinted in a comment there might be slight cons using INSERT .. ON CONFLICT way.
In case table using auto-increment and this query happens a lot, then WITH might be a better option.
See more: https://stackoverflow.com/a/39000072/1161463

这篇关于在Posgres 9.5中使用带有WITH聚类的upsert时，如何检索插入行的ID？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！