Ad

Postgres Syntax Error In IF Statement With NOT EXIST Statement

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.

Ad

Answer

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
);
Ad
source: stackoverflow.com
Ad