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;
Best Answer
You can try
insert ... select
:But: is there a reason preventing you from using
on conflict
instead?