PostgreSQL IF statement

Question

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); 

Answer 1

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.

Answer 2

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