PostgreSQL results of conditional insert

Question

I've got a simple query which inserts data to table when value (unique) does not exist:

DO $$
BEGIN
 IF NOT EXISTS (
 SELECT 1 FROM mytable WHERE hash = 'somelonghash'
 )
 THEN
 INSERT INTO mytable (id, hash, status, datecreated)
 VALUES (123, 'somelonghash', 2, '2018-01-01T12:34:56.789Z');
 END IF;
END $$;

But if I want to receive results like "RETURNING *" if there was an insert, I receive error: "query has no destination for result data". I've tried something like:

DO $$
BEGIN
 IF NOT EXISTS (
 SELECT 1 FROM mytable WHERE hash = 'somelonghash'
 )
 THEN
 INSERT INTO mytable (id, hash, status, datecreated)
 VALUES (123, 'somelonghash', 2, '2018-01-01T12:34:56.789Z')
 RETURNING *;
 END IF;
END $$;

or even like

DO $$
BEGIN
 IF NOT EXISTS (
 SELECT 1 FROM mytable WHERE hash = 'somelonghash'
 )
 THEN
 WITH ins_res AS (
 INSERT INTO mytable (id, hash, status, datecreated)
 VALUES (123, 'somelonghash', 2, '2018-01-01T12:34:56.789Z')
 RETURNING *;
 ) SELECT * FROM ins_res;
 END IF;
END $$;

I assume I'm missing SELECT statement somewhere at the very beginning, like SELECT * FROM (IF-THEN-ELSE results), but can't figure out where. Also this query without IF-THEN works:

WITH ins_res AS (
 INSERT INTO mytable (id, hash, status, datecreated)
 VALUES (123, 'somelonghash', 2, '2018-01-01T12:34:56.789Z')
 RETURNING *
) SELECT * FROM ins_res;

• user1822: Why don't you using insert ... on conflict do nothing?

Answer 1

You can try insert ... select:

 INSERT INTO mytable (id, hash, status, datecreated)
 SELECT 123, 'somelonghash', 2, '2018-01-01T12:34:56.789Z'
 WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE hash = 'somelonghash')
 RETURNING *;

But: is there a reason preventing you from using on conflict instead?

• Naryck: The reason is that every ON CONFLICT attempt adds id and if this operation goes every 1 minute ids numbers are becoming enormous. Oh, wait. Looks like it works