Re: How to use "WHERE NOT EXISTS in INSERT STATEMENT"

On Fri, Feb 19, 2016 at 8:00 PM, Serge Christian Ibala
<christian.ibala@gmail.com> wrote:
> Hello All,
>
> I want to insert some data in my data base (postgrsql) using Python but i
> want to check first that the row does not already exist.
> The normal command is:
>
> cur.execute("INSERT INTO COMPANYLIST (ID, NAME, AGE, ADDRESS, SALARY) \
>         VALUES (1, 'Paul', 32, 'California', 20000.00)");
>
>
> I get the error below when running the code twice.
>
> ===================================================
>
> psycopg2.IntegrityError: duplicate key value violates unique constraint
> "companylist_pkey"
> DETAIL:  Key (id)=(1) already exists.
>
> ====================================================
>
>
> I have tried to replace it by:
>
> cur.execute("INSERT INTO COMPANYLIST (ID, NAME, AGE, ADDRESS, SALARY) \
>         WHERE NOT EXISTS (SELECT 1 FROM table WHERE id='1')\
>         VALUES (1, 'Paul', 32, 'California', 20000.00)");
>
>
> It does not work. Can anybody please help?

As already pointed out this is not the right ML for this kind of
questions: you should use pgsql-general or pgsql-novice.

Your query should probably be:

    INSERT INTO COMPANYLIST (ID, NAME, AGE, ADDRESS, SALARY)
    SELECT 1, 'Paul', 32, 'California', 20000.00
    WHERE NOT EXISTS (SELECT 1 FROM table WHERE id='1')

-- Daniele