Menu
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); 
297
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.
Syntax: CASE WHEN boolean-expression-1 THEN statements [ WHEN boolean-expression-2 THEN statements ... ] [ ELSE statements ] END CASE; The searched CASE statement executes statements based on the result of Boolean expressions in each WHEN clause.
The PostgreSQL CASE expression is the same as IF/ELSE statement in other programming languages. It allows you to add if-else logic to the query to form a powerful query. Since CASE is an expression, you can use it in any places where an expression can be used e.g., SELECT , WHERE , GROUP BY , and HAVING clause.
PL/pgSQL provides you with three forms of the if statements.
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) ... 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.
172
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
