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.
answered Jul 2, 2012 at 19:28
Erwin Brandstetter
668k159 gold badges1.2k silver badges1.3k bronze badges
3 Comments
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.
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
1 Comment
Hubert
This doesn't exactly answer the question. IF statement is different than CASE WHEN. CASE cannot be used outside the query.
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:
Comments
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;
6 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.
Surya
I am also getting the same error
ERROR: syntax error at or near "if" –
sprutex
all who got
ERROR - this is because you should use it inside function. Moreover - If you use 9.0+ then you can use DO to run in it in place by executing an inline function: DO $$ BEGIN ... END $$; |
SELECT CASE WHEN 50<100 THEN 5 ELSE 10 END;which allows a:SELECT CASE WHEN 50<(select count(*) from sometable) THEN 5 ELSE 10 END from mytable;