Обсуждение: How to use "WHERE NOT EXISTS in INSERT STATEMENT"

On 02/19/2016 03:00 PM, Serge Christian Ibala 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?

This is really a SQL question rather than a psycopg2 question. You
didn't explain what you mean by "It does not work." However, it's easy
to see that you've passed bad SQL syntax to Postgres. When
troubleshooting, you need to understand the difference between an error
originating from Postgres and one originating from psycopg2. In this
case, you should have seen a psycopg2.ProgrammingError, which means the
error came from Postgres.

Read the Postgres docs to understand your SQL syntax error and how to
fix it:

http://www.postgresql.org/docs/9.2/static/sql-insert.html

If you are using Postgres 9.5, you may be able to use the brand new ON
CONFLICT clause to get the effect you want.

--
Jonathan Rogers
Socialserve.com by Emphasys Software
jrogers@emphasys-software.com

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