Postgres. Insert multiple rows which don`t exist-CodePudding

I have pairs like (<first_value>, <second_value>) and I have table with following structure:

_____________________________________
| id | first_column | second_column |

I need to insert all pairs which do not exist already. I think that I need something like INSERT IF NOT EXIST but for every pair.

I try use this code:

INSERT INTO <table_name>(<first_column>, <second_column>) 
VALUES (CASE 
            WHEN NOT EXISTS (
                SELECT 1 
                FROM <table_name>
                WHERE <first_column> = <pair_1_value_1> AND <second_column> = <pair_1_value_2>
            ) THEN (<pair_1_value_1>, <pair_1_value_2>) 
    
            WHEN NOT EXISTS (
                SELECT 1 
                FROM <table_name>
                WHERE <first_column> = <pair_2_value_1> AND <second_column> = <pair_2_value_2>
            ) THEN (<pair_2_value_1>, <pair_2_value_2>) 

            .....

         END
);

But I get this error: INSERT has more target columns than expressions. Also, I thought it wouldn't work because it would only insert the one line that first passes the condition. As a if - elif - else operators in other programing languages

CodePudding user response：

If both your conditions are false, your query attempts to insert zero values into two columns. That is not going to fit. In that case you need to insert zero rows with two columns.

There is no IF in SQL; don't try to emulate it. There is WHERE :

CREATE TABLE omg
        ( first_column text
        , second_column text
        );

WITH wtf (one,two) AS (
        VALUES ( 'aa', 'ab')
             , ( 'ba', 'bb')
        )
INSERT INTO omg(first_column, second_column)
SELECT one, two
FROM wtf w
WHERE NOT EXISTS (
        SELECT*
        FROM omg nx
        WHERE nx.first_column = w.one
        AND nx.second_column = w.two
        )
        ;

In your actual code it is probably better to:

create a temp table (CREATE TEMP TABLE wtf as SELECT * FROM omg where 0=1)
insert all the values (from your Python code) into this temp table
select (distinct) from this temp table (instead of from the wtf CTE)