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;