Search code examples
sqlpostgresqlif-statementplpgsql

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);
Solution 
  • 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.