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

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

Solution 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

Solution 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

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

View more solutions

Comments

[Vladimir Tsukanov]

Yes, i tried it, but it doesn't work. ERROR: syntax error at or near "if"

[Milen A. Radev]

1. please don't link beta docs; 2. your example needs to be enclosed in DO to be usable as a SQL statement and not inside a PL/pgSQL function.

[Woot4Moo]

@MilenA.Radev it was the most recent documentation, available on the site. Further I was building off of the OP's initial post to show the correct syntax for the statement.

[Woot4Moo]

@VladimirTsukanov are you assigning the value from the select into a variable?

[Luffydude]

Doesn't work. I got ERROR: syntax error at or near "%" LINE 89: %do%

[Erwin Brandstetter]

@Luffydude: And where would you find % in my code? IOW: The above statements work, you introduced an unrelated syntax error. Looks like you typed %do% instead of $do$.

[Jeb50]

Wondering why PostgreSQL can't make it easier like T-SQL? An obstacle for many wants to jump on pg.

[Surya]

I am also getting the same error ERROR: syntax error at or near "if" –

[Hubert]

This doesn't exactly answer the question. IF statement is different than CASE WHEN. CASE cannot be used outside the query.