SELECT or INSERT a row in one command

Question

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.

Answer 1

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).

Answer 2

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
);

Answer 3

you can use a stored procedure

IF (SELECT id FROM mytable WHERE other_key = 'SOMETHING' LIMIT 1) < 0 THEN
 INSERT INTO mytable (other_key) VALUES ('SOMETHING')
END IF

Answer 4

I have an alternative to Denis answer, that I think is less database-intensive, although a bit more complex:

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

WITH table_sel AS (
    SELECT id
    FROM mytable
    WHERE other_key = 'test'
    UNION
    SELECT NULL AS id
    ORDER BY id NULLS LAST
    LIMIT 1
), table_ins AS (
    INSERT INTO mytable (id, other_key)
    SELECT
        COALESCE(id, NEXTVAL('mytable_id_seq'::REGCLASS)),
               'test'
    FROM table_sel
    ON CONFLICT (id) DO NOTHING
    RETURNING id
)
SELECT * FROM table_ins
UNION ALL
SELECT * FROM table_sel
WHERE id IS NOT NULL;

In table_sel CTE I'm looking for the right row. If I don't find it, I assure that table_sel returns at least one row, with a union with a SELECT NULL.

In table_ins CTE I try to insert the same row I was looking for earlier. COALESCE(id, NEXTVAL('mytable_id_seq'::REGCLASS)) is saying: id could be defined, if so, use it; whereas if id is null, increment the sequence on id and use this new value to insert a row. The ON CONFLICT clause assure that if id is already in mytable I don't insert anything.

At the end I put everything together with a UNION between table_ins and table_sel, so that I'm sure to take my sweet id value and execute both CTE.

This query needs to search for the value other_key only once, and is a "search this value" not a "check if this value not exists in the table", that is very heavy; in Denis alternative you use other_key in both types of searches. In my query you "check if a value not exists" only on id that is a integer primary key, that, for construction, is fast.

Answer 5

Minor tweak a decade late to Denis's excellent answer:

-- Create the table with a unique constraint
CREATE TABLE mytable (
          id serial PRIMARY KEY
,  other_key varchar NOT NULL UNIQUE
);


WITH new_row AS (
  -- Only insert when we don't find anything, avoiding a table lock if
  -- possible.
  INSERT INTO mytable ( other_key )
       SELECT 'SOMETHING'
        WHERE NOT EXISTS (
                SELECT *
                  FROM mytable
                 WHERE other_key = 'SOMETHING'
              )
    RETURNING *
)
(
  -- This comes first in the UNION ALL since it'll almost certainly be
  -- in the query cache. Marginally slower for the insert case, but also
  -- marginally faster for the much more common read-only case.
  SELECT *
    FROM mytable
   WHERE other_key = 'SOMETHING'

  -- Don't check for duplicates to be removed
  UNION ALL

  -- If we reach this point in iteration, we needed to do the INSERT and
  -- lock after all.
  SELECT *
    FROM new_row

) LIMIT 1 -- Just return whatever comes first in the results and allow
          -- the query engine to cut processing short for the INSERT
          -- calculation.
;

The UNION ALL tells the planner it doesn't have to collect results for de-duplication. The LIMIT 1 at the end allows the planner to short-circuit further processing/iteration once it knows there's an answer available.

NOTE: There is a race condition present here and in the original answer. If the entry does not already exist, the INSERT will fail with a unique constraint violation. The error can be suppressed with ON CONFLICT DO NOTHING, but the query will return an empty set instead of the new row. This is a difficult problem because getting that info from another transaction would violate the I in ACID.