Postgres syntax error in IF statement with NOT EXIST statement

Question

I want to use IF statement in insertion.

INSERT INTO <table_name>(<columns>) 
VALUES (IF NOT EXISTS (<exist_statement>) THEN 
               (<first set of values>), 
               (<second set of values>) 
        ELSE 
               (<first set of values>) 
        END IF
);

But the following error appears:

syntax error at or near "NOT"
LINE 1: ...r_id, role_id, role_type, user_status) VALUES (IF NOT EXISTS...
                                                             ^

I tried putting this block of code in the DO:

DO $$ 
BEGIN
<code_block>
END
$$

But the error remains.

@a_horse_with_no_name. But I found it in the postgres documentation. Section 43.6 in 14 version — Prosto_Oleg
– Prosto_Oleg, Commented Mar 17, 2022 at 14:53
Not sure which version of the manual you refer to, but that's most likely the section about the PL/pgSQL procedural language you were looking at. The SQL language does not have an IF — user330315
– user330315, Commented Mar 17, 2022 at 14:58

Andronicus · Accepted Answer · 2022-03-17 11:21:49Z

1

You need to use case:

INSERT INTO <table_name>(<columns>) 
VALUES (CASE WHEN NOT EXISTS (<exist_statement>) THEN 
               (<first set of values>), 
               (<second set of values>) 
        ELSE 
               (<fist set of values>) 
        END
);

answered Mar 17, 2022 at 11:21

Andronicus

26.2k18 gold badges55 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres syntax error in IF statement with NOT EXIST statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related