Postgres: Conditionally adding to table

Question

I'm having trouble figuring out how to insert a row in a table exclusively if a specific condition is true using Postgres.

I've found a lot of answers that use CONFLICT, but the value I'm checking for isn't unique, so I can't do much with that. I've also tried IF ELSE but as far as I understand it Postgres doesn't support this?

This is my current query:

IF EXISTS (SELECT id FROM "Vote" as v WHERE v."createdAt" >= '2020-09-01' AND v."createdAt" < '2020-10-01' AND v."authorId" = 6667 )                                                                         
    PRINT 'do nothing'
ELSE 
    INSERT INTO "Vote" ("authorId", "serverId") VALUES (1, 1);

It returns:

ERROR:  syntax error at or near "IF"

Essentially I'm checking if a user has cast a vote between two dates, if that is true, then nothing should happen, if false the vote should be inserted in the table.

Is checking if something should be inserted and inserting it possible in a single query, or should I first check if the value exists and then run the insert query?

score 2 · Accepted Answer · answered Dec 05 '20 at 23:21

2

Using plain SQL:

INSERT INTO "Vote" ("authorId", "serverId")
SELECT 1, 1
WHERE NOT EXISTS (
    SELECT id
    FROM "Vote" as v
    WHERE
        v."createdAt" >= '2020-09-01' AND
        v."createdAt" < '2020-10-01' AND
        v."authorId" = 6667)

There is no IF in the plain SQL, only in pl/sql

answered Dec 05 '20 at 23:21

Abelisto

14,826
2
33
41

This still seems to insert even though a record created between `'2020-09-01'` and `'2020-10-01'` exists – lpetrucci Dec 05 '20 at 23:26
@Eight Was my mistake. Should be `not exists`. Already fixed. – Abelisto Dec 05 '20 at 23:27
1

@Eight There was a typo in the answer now corrected. Please try with the updated `WHERE NOT EXISTS` – Mike Organek Dec 05 '20 at 23:27
I had completely missed your update, that works! Thank you so much. Will set as answer as soon as Stackoverflow allows me to :) – lpetrucci Dec 05 '20 at 23:29
@Eight PS: Be careful about [race condition](https://en.wikipedia.org/wiki/Race_condition#Software). You can find solutions in the [www](https://duckduckgo.com/?q=postgresql+race+condition) – Abelisto Dec 05 '20 at 23:38
@Abelisto How would I write this with `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;`? Adding `Begin;` the above at the start and `Commit;` at the end seems to completely ignore my checks. If it's not a simple fix no worries, I'll open another question. – lpetrucci Dec 06 '20 at 00:00
@Eight Try to use [`start transaction`](https://www.postgresql.org/docs/current/sql-start-transaction.html) with appropriate transaction_mode – Abelisto Dec 06 '20 at 00:21
@Eight Actually you need something like [this answer](https://stackoverflow.com/a/21470743/593144) for your case. – Abelisto Dec 06 '20 at 00:37

Postgres: Conditionally adding to table

1 Answers1