PostgreSQL IF statement
DO$do$BEGIN IF EXISTS (SELECT FROM orders) THEN DELETE FROM orders; ELSE INSERT INTO orders VALUES (1,2,3); END IF;END$do$
There are no procedural elements in standard SQL. The IF
statement is part of the default procedural language PL/pgSQL. You need to create a function or execute an ad-hoc statement with the DO
command.
You need a semicolon (;
) at the end of each statement in plpgsql (except for the final END
).
You need END IF;
at the end of the IF
statement.
A sub-select must be surrounded by parentheses:
IF (SELECT count(*) FROM orders) > 0 ...
Or:
IF (SELECT count(*) > 0 FROM orders) ...
This is equivalent and much faster, though:
IF EXISTS (SELECT FROM orders) ...
Alternative
The additional SELECT
is not needed. This does the same, faster:
DO$do$BEGIN DELETE FROM orders; IF NOT FOUND THEN INSERT INTO orders VALUES (1,2,3); END IF;END$do$
Though unlikely, concurrent transactions writing to the same table may interfere. To be absolutely sure, write-lock the table in the same transaction before proceeding as demonstrated.
Just to help if anyone stumble on this question like me,if you want to use if in PostgreSQL, you use "CASE"
select case when stage = 1 then 'running' when stage = 2 then 'done' when stage = 3 then 'stopped' else 'not running' end as run_status from processes
From the docs
IF boolean-expression THEN statementsELSE statementsEND IF;
So in your above example the code should look as follows:
IF select count(*) from orders > 0THEN DELETE from ordersELSE INSERT INTO orders values (1,2,3);END IF;
You were missing: END IF;