SELECT or INSERT a row in one command

postgresql

I'm using PostgreSQL 9.0 and I have a table with just an artificial key (auto-incrementing sequence) and another unique key. (Yes, there is a reason for this table. :)) I want to look up an ID by the other key or, if it doesn't exist, insert it:

SELECT id
FROM mytable
WHERE other_key = 'SOMETHING'

Then, if no match:

INSERT INTO mytable (other_key)
VALUES ('SOMETHING')
RETURNING id

The question: is it possible to save a round-trip to the DB by doing both of these in one statement? I can insert the row if it doesn't exist like this:

INSERT INTO mytable (other_key)
SELECT 'SOMETHING'
WHERE NOT EXISTS (SELECT * FROM mytable WHERE other_key = 'SOMETHING')
RETURNING id

... but that doesn't give the ID of an existing row. Any ideas? There is a unique constraint on other_key, if that helps.

Have you tried to union it?

Edit - this requires Postgres 9.1:

create table mytable (id serial primary key, other_key varchar not null unique);

WITH new_row AS (
INSERT INTO mytable (other_key)
SELECT 'SOMETHING'
WHERE NOT EXISTS (SELECT * FROM mytable WHERE other_key = 'SOMETHING')
RETURNING *
)
SELECT * FROM new_row
UNION
SELECT * FROM mytable WHERE other_key = 'SOMETHING';

results in:

 id | other_key 
----+-----------
  1 | SOMETHING
(1 row)

No, there is no special SQL syntax that allows you to do select or insert. You can do what Ilia mentions and create a sproc, which means it will not do a round trip fromt he client to server, but it will still result in two queries (three actually, if you count the sproc itself).

using 9.5 i successfully tried this

based on Denis de Bernardy's answer
only 1 parameter
no union
no stored procedure
atomic, thus no concurrency problems (i think...)

The Query:

WITH neworexisting AS (
    INSERT INTO mytable(other_key) VALUES('hello 2') 
    ON CONFLICT(other_key) DO UPDATE SET existed=true -- need some update to return sth
    RETURNING * 
)
SELECT * FROM neworexisting

first call:

id|other_key|created            |existed|
--|---------|-------------------|-------|
 6|hello 1  |2019-09-11 11:39:29|false  |

second call:

id|other_key|created            |existed|
--|---------|-------------------|-------|
 6|hello 1  |2019-09-11 11:39:29|true   |

First create your table ;-)

CREATE TABLE mytable (
    id serial NOT NULL,
    other_key text NOT NULL,
    created timestamptz NOT NULL DEFAULT now(),
    existed bool NOT NULL DEFAULT false,
    CONSTRAINT mytable_pk PRIMARY KEY (id),
    CONSTRAINT mytable_uniq UNIQUE (other_key) --needed for on conflict
);

SELECT or INSERT a row in one command

Related

Recent Posts