PostgreSQL IF statement

Question

How can I do such query in Postgres   IF  select count    from orders   gt  0 THEN   DELETE from orders ELSE    INSERT INTO orders values  1 2 3

User · Accepted Answer

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   gt  0      Or      IF  SELECT count     gt  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

User · Answer

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     http   www postgresql org docs current static sql-do html https   www postgresql org docs current static plpgsql-control-structures html

User · Answer

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  gt  0 THEN   DELETE from orders ELSE    INSERT INTO orders values  1 2 3   END IF    You were missing  END IF

User · Answer

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

[sql] PostgreSQL IF statement

Alternative

Examples related to sql

Examples related to postgresql

Examples related to if-statement

Examples related to plpgsql