current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
    
Your example doesn't make sense. The not exists will always fail because everything is from the same table. –  Gordon Linoff Jun 24 at 20:16
add comment

1 Answer

up vote 0 down vote 
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
    );
"""
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.