On conflict delete postgres

Question

I am trying to toggle a relation in a single query where it deletes the relation if it already exists, otherwise it creates it. I could add a third column with a bool that toggles it but I would rather delete it.

Schema

CREATE TABLE IF NOT EXISTS thread_subscription (
    profile_id     INTEGER REFERENCES profile (id),
    thread_id      INTEGER REFERENCES thread (id),
    UNIQUE(profile_id,thread_id)
)

Query

INSERT INTO thread_subscription (profile_id,thread_id) 
VALUES ($1,$2) ON CONFLICT (profile_id,thead_id) 
DO DELETE FROM thread_subscription 
WHERE profile_id = $1 AND thread_id = $2;

Answer 1

So your intent is to run an INSERT order on a table, and you expect that on duplicate keys it will actually DELETE the related record. While technically feasible, I would not recommend this set up, because this is action at a distance, which is hard to debug.

The same type of functionnality however could be achieved with a Postgres function. This makes the intent explicit when it comes to toggling the subscription, and does not interfere with standard database statements (INSERT).

Here is the code for the function : it takes two parameters as input, verifies if a record is already there in the subscriptions table, and then performs the relevant operation ; it returns 0 on DELETE and 1 on INSERT. You can see this db fiddle for a full demo of how it works.

CREATE OR REPLACE FUNCTION toggle_subscription(
    pid NUMERIC, 
    tid NUMERIC
) 
RETURNS NUMERIC AS $$
DECLARE
    row_exists NUMERIC;
BEGIN

    SELECT 1 
    INTO row_exists 
    FROM thread_subscription 
    WHERE profile_id = pid and thread_id = tid;

    IF (row_exists > 0) THEN
        DELETE FROM thread_subscription WHERE profile_id = pid and thread_id = tid;
        RETURN 0;
    ELSE
        INSERT INTO thread_subscription(profile_id, thread_id) VALUES(pid, tid);
        RETURN 1;
    END IF;

END; 
$$
LANGUAGE plpgsql;