Insert if not exists, else return id in postgresql

Question

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

id serial primary key
key varchar
value varchar

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?

possible duplicate of Return id if a row exists, INSERT otherwise — Erwin Brandstetter
– Erwin Brandstetter, Commented May 7, 2014 at 20:50
Both the question and the answers here are much better voted than the ones there and the number of visits here is much higher so if some question must be closed it is the other not this one. — Clodoaldo Neto
– Clodoaldo Neto, Commented May 9, 2014 at 23:55
FYI Postgres 9.5 brings an UPSERT feature. See blog post by Craig Kerstiens. — Basil Bourque
– Basil Bourque, Commented Jul 17, 2015 at 2:41
Possible duplicate of Postgres: INSERT if does not exist already — Gajus
– Gajus, Commented Oct 22, 2018 at 15:47

Clodoaldo Neto · Accepted Answer · 2014-05-13 14:59:47Z

95

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.

edited May 13, 2014 at 14:59

answered Aug 12, 2013 at 17:14

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

aright Over a year ago

I realized at closer inspection that this solution does not actually do what I was hoping since it does not return anything unless the INSERT works (i.e. if the key-value pair was not previously in the table). If the row was already in the table, it just returns blank (not the ID as intended). The reason I missed that earlier was that I accidentally made a typo when I was testing it (so that key-value pair wasn't in the table, and therefore it returned the ID of the row).

wildplasser Over a year ago

BTW: could be UNION ALL , IMHO.

Clodoaldo Neto Over a year ago

@wildplasser Notice that there will be always only one row returned regardless of the use of all.

wildplasser Over a year ago

Semantically you're right. But I seriously doubt if the optimiser will realise it. (it is futile, of course)

wildplasser Over a year ago

For a single row it will be neglectible. But it could influence the plan generation/selection (which would only be detectible if more than one rowe were involved) Removing the duplicates is not that costly ansich but it could prossibly force the two subplans to yield their RETURNING in a particular order (which is not needed)

|

roman · Accepted Answer · 2013-08-12 17:30:03Z

6

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

answered Aug 12, 2013 at 17:30

roman

118k30 gold badges205 silver badges209 bronze badges

Comments

Wutikrai · Accepted Answer · 2013-11-08 01:48:05Z

-1

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.

answered Nov 8, 2013 at 1:48

Wutikrai

393 bronze badges

Collectives™ on Stack Overflow

Insert if not exists, else return id in postgresql

3 Answers 3

13 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

13 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related