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;
insert ... on conflict do nothing
? – a_horse_with_no_name Jan 2 '19 at 15:24