如果不存在则插入，否则在PostgreSQL中返回ID [英] Insert if not exists, else return id in postgresql

问题描述

我在PostgreSQL中有一个简单的表，该表有三列:

I have a simple table in PostgreSQL that has three columns:

• id串行主键
• 密钥varchar
• 值varchar

我已经在SO上看到了以下问题:插入PostgreSQL中的重复更新吗? /a>，但我想知道如何获取ID(如果存在)，而不是进行更新.如果标准做法是始终插入"或更新(如果存在)"，那是为什么呢?进行SELECT(LIMIT 1)的成本是否比进行UPDATE的成本高?

I have already seen this question here on SO: Insert, on duplicate update in PostgreSQL? but I'm wondering just how to get the id if it exists, instead of updating. If the standard practice is to always either "insert" or "update if exists", why is that? Is the cost of doing a SELECT (LIMIT 1) greater than doing an UPDATE?

我有以下代码

INSERT INTO tag
    ("key", "value")
    SELECT 'key1', 'value1'
WHERE
    NOT EXISTS (
        SELECT id,"key","value" FROM tag WHERE key = 'key1' AND value = 'value1'
    );

在某种意义上说它不存在(如果存在)，但我想获取ID.是否有"RETURNING id"子句或可以在其中输入的类似内容?

which works in the sense that it doesn't insert if exists, but I'd like to get the id. Is there a "RETURNING id" clause or something similar that I could tap in there?

推荐答案

是的，有returning

INSERT INTO tag ("key", "value")
SELECT 'key1', 'value1'
WHERE NOT EXISTS (
    SELECT id, "key", "value"
    FROM node_tag
    WHERE key = 'key1' AND value = 'value1'
    )
returning id, "key", "value"

如果该行已存在，则返回该行

To return the row if it already exists

with s as (
    select id, "key", "value"
    from tag
    where key = 'key1' and value = 'value1'
), i as (
    insert into tag ("key", "value")
    select 'key1', 'value1'
    where not exists (select 1 from s)
    returning id, "key", "value"
)
select id, "key", "value"
from i
union all
select id, "key", "value"
from s

如果该行不存在，则将返回插入的行，否则返回现有的行.

If the row does not exist it will return the inserted one else the existing one.

顺便说一句，如果键"/值"对使其唯一，则它是主键，并且不需要id列.除非键"/值"对中的一个或两个都可以为空.

BTW, if the pair "key"/"value" makes it unique then it is the primary key, and there is no need for an id column. Unless one or both of the "key"/"value" pair can be null.

这篇关于如果不存在则插入，否则在PostgreSQL中返回ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！