CODEWITHSUNDEEP
sqlpostgresql
undefined
undefined

Reputation: 3632

INSERT into table if doesn't exists and return id in both cases

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

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

id    appid    name
1      2        abc
2      2        cde

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

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.

For ex 

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

This doesn't return anything.

Upvotes: 3

Views: 433

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can do:

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;

Upvotes: 4

Related Questions