Search Postgresql Archives

Re: Insert data if it is not existing

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 






On Thu, May 24, 2018 at 10:51 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 05/23/2018 06:03 PM, tango ward wrote:

On Thu, May 24, 2018 at 8:19 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@aklaver.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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@aklaver.com>




Updated my code to this:

curr.pgsql.execute('''
INSERT INTO my_table(name, age)
SELECT %s, %s

The above is not going to work as you cannot use %s to substitute for identifiers, in this case the column names name and age.


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

Try the example I showed previously. If you do not want to use the the named parameters e.g %(name)s then use use %s and a tuple like:

'''
INSERT INTO my_table(%s, %s)
WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= %s)
''', (Scott', 23, 'Scott'))





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





--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx

Thank you Master, the name=%s solved it.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux