CODEWITHSUNDEEP
postgresql
aright
aright

Reputation: 2124

Insert if not exists, else return id in postgresql

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

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?

I have the following code

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

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?

Upvotes: 64

Views: 59717

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

Yes there is 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.

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.

Upvotes: 95

Wutikrai
Wutikrai

Reputation: 39

And you can store value returned to variables in form of ... RETURNING field1, field2,... INTO var1, var2,...

RETURNING will normally return a query which would return Error 'query has no destination for result data' if you call it in plpgsql without using its returned result set.

Upvotes: -1

roman
roman

Reputation: 117345

with vals as (
  select 'key5' as key, 'value2' as value
)
insert into Test1 (key, value)
select v.key, v.value
from vals as v
where not exists (select * from Test1 as t where t.key = v.key and t.value = v.value)
returning id

sql fiddle demo

Upvotes: 6

Related Questions