0

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

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?

  • 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 – Naryck Jan 2 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.