CODEWITHSUNDEEP
sqlpostgresqlpsycopg2
Brendan Murphy
Brendan Murphy

Reputation: 77

Dynamic conditional insert in postgresql

I am attempting to define a conditional insert in postgresql, on an index over 3 columns (which gives uniqueness). I'm trying to follow the following example from the postgresql documentation:

 INSERT INTO example_table
     (id, name)
 SELECT 1, 'John'
 WHERE
     NOT EXISTS (
         SELECT id FROM example_table WHERE id = 1
     );

For the basic SELECT WHERE NOT EXISTS structure. But if the index varies, i.e. you want to prevent insert if there exists a selection in the table with id=index value of current pre-insert row, how do you implement this? Here is my current (wrong) code:

insert = (
"INSERT INTO table (index,a,b,c,d,e)"
"SELECT * FROM table WHERE NOT EXISTS (SELECT * FROM table WHERE index=index)");
cur.execute(insert,data)

For clarity, the index is defined on data columns (a,b,c), data is a row of (index,a,b,c,d,e), and I'm wrapping this in psycopg2. I have searched for an answer for a while, but haven't been able to successfully adapt anything to this problem yet.

Upvotes: 0

Views: 2220

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

insert into t1 (a, b, c, d, e)
select a, b, c, d, e
from t2
where not exists (
    select 1
    from t1
    where a = t2.a and b = t2.b and c = t2.c
);

In Python it is easier and cleaner to use the triple quote raw string

insert = """
    insert into t1 (a, b, c, d, e)
    select a, b, c, d, e
    from t2
    where not exists (
        select 1
        from t1
        where a = t2.a and b = t2.b and c = t2.c
    );
"""

Upvotes: 2

Related Questions