Dynamic conditional insert in postgresql

Question

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.

Your example doesn't make sense. The not exists will always fail because everything is from the same table. — Gordon Linoff
– Gordon Linoff, Commented Jun 24, 2014 at 20:16

Clodoaldo Neto · Accepted Answer · 2014-06-24 20:27:04Z

2

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
    );
"""

answered Jun 24, 2014 at 20:27

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Collectives™ on Stack Overflow

Dynamic conditional insert in postgresql

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