Postgres. Insert multiple rows which don`t exist

Question

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

I guess,you can read in PostgreSQL-manual about ON CONFLICT clause — Sergey
– Sergey, Commented Mar 19, 2022 at 17:20
@Sergey. But I do not have conflict. First and second values is not unique in table. But I need unique pairs. (1, 1) (2,1) or (1, 1) (1,2) - Okay. (1,1) (1,1) - Not okay — Prosto_Oleg
– Prosto_Oleg, Commented Mar 19, 2022 at 17:26
@Serg pairs from python dict. And I wouldn't want to query all the pairs first, check uniqueness through python, and then add unique ones. I would like to do everything in sql — Prosto_Oleg
– Prosto_Oleg, Commented Mar 19, 2022 at 17:28
<table_name>(<first_column>, <second_column>) BTW: please don't use meta-syntax or pseudocode. Use actual code, with actual names. — wildplasser
– wildplasser, Commented Mar 19, 2022 at 18:34

wildplasser · Accepted Answer · 2022-03-19 19:30:06Z

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
        , PRIMARY KEY (first_column, second_column)
        );

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)

marcothesane · Accepted Answer · 2022-03-20 05:12:48Z

Doesn't the MERGE statement do exactly what you need? This example seems to work as you describe, at least to me:

CREATE TABLE tgt (a,b) AS (
            SELECT 'A','A'
  UNION ALL SELECT 'B','B'
  UNION ALL SELECT 'C','C'
  UNION ALL SELECT 'D','D'
  UNION ALL SELECT 'E','E'
  UNION ALL SELECT 'F','F'
  UNION ALL SELECT 'G','G'
  UNION ALL SELECT 'H','H'
  UNION ALL SELECT 'I','I'
  UNION ALL SELECT 'J','J'
)
;
-- out CREATE TABLE
MERGE INTO tgt
USING (
  SELECT 'A','A' UNION ALL
  SELECT 'K','K' UNION ALL
  SELECT 'L','L'
) src(a,b)
  ON src.a=tgt.a
 AND src.b=tgt.b
WHEN NOT MATCHED THEN
  INSERT VALUES (src.a,src.b)
;
-- out  OUTPUT 
-- out --------
-- out       2
SELECT * FROM tgt;
-- out  a | b 
-- out ---+---
-- out  A | A
-- out  B | B
-- out  C | C
-- out  D | D
-- out  E | E
-- out  F | F
-- out  G | G
-- out  H | H
-- out  I | I
-- out  J | J
-- out  K | K
-- out  L | L

Collectives™ on Stack Overflow

Postgres. Insert multiple rows which don`t exist

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related