Insert new row and get primary key or get primary key of existing record

Question

I have a simple tag table in Postgres 9.3:

CREATE TABLE tag (
  id SERIAL PRIMARY KEY,
  text TEXT NOT NULL UNIQUE
);

Currently I can insert a new tag and get back the new id easily enough:

INSERT INTO tag (text) VALUES ('hey') RETURNING id

However I would like to check that the tag doesn't already exist (unique constraint) and if it does exist, return the id of the existing tag.

I attempted to use COALESCE to achieve this:

SELECT COALESCE(
    (SELECT id FROM tag WHERE text='hey'),
    (INSERT INTO tag (text) VALUES ('hey') RETURNING id)
)

But unfortunately while I believe this logic is sound it doesn't look like I can have an INSERT statement in a COALESCE (generates a syntax error at INTO).

What's the easiest way to accomplish this?

possible duplicate of [Insert if not exists, else return id in postgresql](http://stackoverflow.com/questions/18192570/insert-if-not-exists-else-return-id-in-postgresql) — , May 07 '14 at 17:46
possible duplicate of [Return id if a row exists, INSERT otherwise](http://stackoverflow.com/questions/10057668/return-id-if-a-row-exists-insert-otherwise) — Erwin Brandstetter, May 07 '14 at 20:50

score 0 · Answer 1 · answered May 07 '14 at 17:30

0

This should give you the same result. If the row doesn't exist, then create it. After that part executes, the row will definitely exist and we select its id.

IF NOT EXISTS (SELECT id FROM tag WHERE text = 'hey')
BEGIN
    INSERT INTO tag (text) VALUES ('hey')
END
SELECT id FROM tag WHERE text = 'hey'

answered May 07 '14 at 17:30

How 'bout a Fresca

2,267
1
15
26

Sorry, I don't know the differences between PostgreSQL and MSSQL. I should have looked into that before answering. It is valid SQL for some platforms (MSSQL) so please be more specific with your criticism to make it more constructive. – How 'bout a Fresca May 07 '14 at 18:01
It might be valid `T-SQL` but certainly not valid `SQL`. There is no `IF` statement in **SQL** (and no `BEGIN` or `END` either). – May 07 '14 at 18:36
Got it. From the syntax of the original post I inferred that it was t-sql or pl/sql. Not trying to argue, just explain. I'll keep this in mind in the future :) – How 'bout a Fresca May 07 '14 at 20:25

score 0 · Accepted Answer · edited May 23 '17 at 10:32

Credit goes to Clodoaldo Neto for his answer.

I'm putting my answer here because while his answer did point me in the direction it is slightly different, and I've simplified mine a bit.

WITH 
    existing AS (SELECT id FROM tag WHERE text='hey'),
    new AS (INSERT INTO tag (text) SELECT 'hey' WHERE NOT EXISTS (SELECT 1 FROM existing) RETURNING id)

SELECT id FROM existing UNION ALL SELECT id FROM new

Insert new row and get primary key or get primary key of existing record

2 Answers2