Re: Insert data if it is not existing

On Thu, May 24, 2018 at 8:19 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 05/23/2018 05:11 PM, tango ward wrote:
>
>> Sorry, i forgot the values.
>>
>> curr.pgsql.execute('''
>> INSERT INTO my_table(name, age)
>> SELECT name, age
>> WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name)
>> ''', ('Scott', 23))
>>
>
> Pretty sure this would throw an exception as there are no parameter
> markers in the query for the parameter values in the tuple to bind to. So
> are you swallowing the exception in you code?
>
>
>
>> Sorry, I don't understand, where should I place the from clause? I just
>> saw a sample code like this in SO, so I gave it a shot
>>
>
> Not tested:
> '''
> INSERT INTO my_table(%(name)s, %(age)s)
> WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= %(name))
> ''', {'name': Scott', 'age': 23})
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

Updated my code to this:

curr.pgsql.execute('''
INSERT INTO my_table(name, age)
SELECT %s, %s
WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name)
''', ('Scott', 23))

If I remove SELECT statement, I will get an error message: error :
psycopg2.ProgrammingError: syntax error at or near "WHERE"
LINE 12: WHERE NOT EXISTS

Trying to coordinate with Lead Dev about adding Index On The Fly