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;

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

user1822
– user1822

2019-01-02 15:24:53 +00:00
Commented Jan 2, 2019 at 15:24 — user1822
– user1822, Commented Jan 2, 2019 at 15:24

mustaccio · Accepted Answer · 2019-01-02 14:27:53Z

2

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?

answered Jan 2, 2019 at 14:27

mustaccio

28.9k24 gold badges60 silver badges77 bronze badges

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
– Naryck

2019-01-02 14:30:22 +00:00
Commented Jan 2, 2019 at 14:30

Add a comment |

Stack Exchange Network

PostgreSQL results of conditional insert

1 Answer 1

Hot Network Questions

PostgreSQL results of conditional insert

1 Answer 1

Related

Hot Network Questions