CODEWITHSUNDEEP
sqlpostgresql
typeof programmer
typeof programmer

Reputation: 1609

Insert and return id no matter the the record exists or not

My requirement is to insert a record into a table if the record doesn't exist, but the SQL needs to return the id no matter the record exists or not. Here is my SQL:

INSERT INTO tags (tag)
                    SELECT 'abc'
                    WHERE NOT EXISTS (SELECT 1 FROM tags WHERE tag = 'abc') RETURNING tag_id;

But it only returns the id if the record doesn't exist, it returns nothing when it has existing records that match the WHERE condition. Please advise. Thanks.

Upvotes: 0

Views: 351

Answers (2)

Abelisto
Abelisto

Reputation: 15614

with
  cte_sel(tag_id) as (
    select tag_id from tags where tag = 'abc'),
  cte_ins(tag_id) as (
    insert into tags (tag)
    select 'abc'
    where not exists (select 1 from cte_sel)
    returning tag_id)
select * from cte_sel
union all
select * from cte_ins;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You would use a CTE to get all the records:

with to_insert as (
      select 'abc' as tag
     ),
     insert into tags (tag)
         select tag
         from to_insert
         where not exists (select 1 from tags where tags.tag = to_insert.tag)
        )
select *
from to_insert;

I would suggest that you on conflict instead of not exists for preventing duplicates.

Upvotes: 1

Related Questions