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); 0 4 Answers Do $Do$...
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);
4 Answers
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:
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;
Marcus Vance is a cybersecurity auditor and technology writer dedicated to educating the public about online safety, data privacy regulations, enterprise security, and emerging cyber threats.
