PostgreSQL IF statement

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

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

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

From the [docs][1]

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; [1]: http://www.postgresql.org/docs/9.2/static/plpgsql-control-structures.html