How can I do such query in Postgres?
IF (select count(*) from orders) > 0
DELETE from orders
INSERT INTO orders values (1,2,3);
DO $do$ BEGIN IF EXISTS (SELECT 1 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
You need a
; at the end of each statement in plpgsql (except for the final
END IF; at the end of the
A sub-select needs to be surrounded by parentheses:
IF (SELECT count(*) FROM orders) > 0 ...
IF (SELECT count(*) > 0 FROM orders) ...
This is equivalent and much faster, though:
IF EXISTS (SELECT 1 FROM orders) ...
You do not actually need an additional
SELECT at all here. This does the same, a bit faster:
DO $do$ BEGIN DELETE FROM orders; IF NOT FOUND THEN INSERT INTO orders VALUES (1,2,3); END IF; END $do$