How can I do such query in Postgres?
IF (select count(*) from orders) > 0
THEN
DELETE from orders
ELSE
INSERT INTO orders values (1,2,3);
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) ...
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.