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

Answer 3

You could also use the the basic structure for the PL/pgSQL CASE with anonymous code block procedure block:

DO $$ BEGIN
    CASE
        WHEN boolean-expression THEN
          statements;
        WHEN boolean-expression THEN
          statements;
        ...
        ELSE
          statements;
    END CASE;
END $$;

References:

1. http://www.postgresql.org/docs/current/static/sql-do.html
2. https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html

Answer 4

From the docs

IF boolean-expression THEN
    statements
ELSE
    statements
END IF;

So in your above example the code should look as follows:

IF select count(*) from orders > 0
THEN
  DELETE from orders
ELSE 
  INSERT INTO orders values (1,2,3);
END IF;

You were missing: END IF;