如果不存在则插入表并在两种情况下返回 id [英] INSERT into table if doesn't exists and return id in both cases

问题描述

我试图在 POSTGRES 中编写一个复杂的查询，这个问题是那个的子查询.

I am trying to write a complex query in POSTGRES, this question is subquery of that.

这是我的表(id 是主键和自动增量):

Here is my table (id is primary key and auto increment):

id    appid    name
1      2        abc
2      2        cde

在此表中，我想获取名称为xyz"且 appid=2 的 id，如果不存在，则插入并返回 ID.

In this table, I want to get id where name is "xyz" and appid=2 and if that doesn't exists, insert and return the ID.

我知道有几个类似的问题有些类似的问题，我已经尝试过，但似乎没有用.

I know there are several similar questions which somewhat same asks, which I already tried but doesn't seems working.

这是我试图执行但没有按预期工作的内容:

This is what I have tried to exsecute which didn't work as expected:

INSERT INTO table_name (appid, name) SELECT 2, 'xyz' WHERE NOT EXISTS (SELECT id from table_name WHERE appid=2 AND name='xyz') returning id

当添加新元素并返回新添加元素的 ID 时，这很有效，但当行已存在时不返回任何内容.

This works well when a new element is added and returns the ID of newly added element but doesn't return anything when a row already exists.

对于前

INSERT INTO table_name (appid, name) SELECT 2, 'abc' WHERE NOT EXISTS (SELECT id from table_name WHERE appid=2 AND name='abc') returning id

这不会返回任何内容.

推荐答案

您可以:

with id as (
      select id
      from table_name
      where appid = 2 and name = 'xyz'
     ),
     i as (
      insert table_name (appid, name)
          select 2, 'xyz' 
          where not exists (select 1 from id)
          returning id
     )
select id
from id
union all
select id
from i;

这篇关于如果不存在则插入表并在两种情况下返回 id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！