PostgreSQL – Results of Conditional Insert Explained

postgresql

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:

    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?