PostgreSQL IF 语句 - 开卷题库

小开

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;

小开

最佳答案

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: