Vladimir Tsukanov Vladimir Tsukanov - 6 months ago 48
SQL Question

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

Answer

Try:

DO
$do$
BEGIN
IF EXISTS (SELECT 1 FROM orders) THEN
   DELETE FROM orders;
ELSE 
   INSERT INTO orders VALUES (1,2,3);
END IF;
END
$do$

Major points

  • 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 ; 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 needs to 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 1 FROM orders) ...
    

Alternative

You do not actually need an additional SELECT at all here. This does the same, a bit faster:

DO
$do$
BEGIN
DELETE FROM orders;
IF NOT FOUND THEN
   INSERT INTO orders VALUES (1,2,3);
END IF;
END
$do$