Re: Insert data if it is not existing

On 25/05/18 14:35, Igor Neyman wrote: '''INSERT INTO my_table(name, age) VALUES( %s, %s) WHERE NOT EXISTS(SELECT name FROM my_table WHERE name=%s)''', ('Scott', 23, 'Scott')) I haven't been following this thread, so maybe this has already been said, but I think you need a SELECT in there

RE: Insert data if it is not existing

From: tango ward [mailto:tangowar...@gmail.com] Sent: Thursday, May 24, 2018 8:16 PM To: Adrian Klaver <adrian.kla...@aklaver.com> Cc: David G. Johnston <david.g.johns...@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org> Subjec

Re: Insert data if it is not existing

On 05/23/2018 06:03 PM, tango ward wrote: 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 :

Re: Insert data if it is not existing

On 05/23/2018 09:39 PM, David G. Johnston wrote: On Wednesday, May 23, 2018, Adrian Klaver > wrote: '''INSERT INTO my_table(name, age) SELECT %s, %s WHERE NOT EXISTS(SELECT name FROM my_table WHERE

Re: Insert data if it is not existing

On Wednesday, May 23, 2018, Adrian Klaver wrote: > >> '''INSERT INTO my_table(name, age) >> SELECT %s, %s >> WHERE NOT EXISTS(SELECT name FROM my_table WHERE name=%s)''', ('Scott', >> 23) >> >> > I doubt that worked, you have three parameter markers(%s) and two >

Re: Insert data if it is not existing

On 05/23/2018 07:59 PM, tango ward wrote: On Thu, May 24, 2018 at 10:51 AM, Adrian Klaver 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

Re: Insert data if it is not existing

On 05/23/2018 08:04 PM, tango ward wrote: On Thu, May 24, 2018 at 10:55 AM, David G. Johnston > wrote: On Wednesday, May 23, 2018, Adrian Klaver > wrote:

Re: Insert data if it is not existing

On 05/23/2018 06:03 PM, tango ward wrote: On Thu, May 24, 2018 at 8:19 AM, Adrian Klaver > wrote: On 05/23/2018 05:11 PM, tango ward wrote: Sorry, i forgot the values. curr.pgsql.execute(''' INSERT

Re: Insert data if it is not existing

On Thu, May 24, 2018 at 9:33 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > > I advise you fiddle with it some more and see if you can stumble upon a > functioning solution. Maybe step away from the problem for a bit, get some > fresh air, maybe sleep on it. You've demostrated

Re: Insert data if it is not existing

On Wednesday, May 23, 2018, tango ward wrote: > > On Thu, May 24, 2018 at 9:09 AM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Wednesday, May 23, 2018, tango ward wrote: >> >>> >>> >>> curr.pgsql.execute(''' >>> INSERT INTO

Re: Insert data if it is not existing

On Thu, May 24, 2018 at 9:09 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wednesday, May 23, 2018, tango ward wrote: > >> >> >> curr.pgsql.execute(''' >> INSERT INTO my_table(name, age) >> SELECT %s, %s >> WHERE NOT EXISTS(SELECT name FROM my_table WHERE

Re: Insert data if it is not existing

On Wednesday, May 23, 2018, tango ward wrote: > > > 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)) > So, WHERE name = name is ALWAYS true and so as long as there

Re: Insert data if it is not existing

On Thu, May 24, 2018 at 8:19 AM, Adrian Klaver 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

Re: Insert data if it is not existing

On 05/23/2018 05:12 PM, tango ward wrote: Sorry I forgot to mention. The table that I am working on right now doesn't have any unique column. AFAIK, I can only use ON CONFLICT if there's an error for unique column. I have not tried it but I believe you can create an INDEX on the fly:

Re: Insert data if it is not existing

If you are going to post so many messages can you please observe the bottom-post and trim convention used of this mailing list. On Wednesday, May 23, 2018, tango ward wrote: > Tried it, but it still I am not inserting data into the table. > tried what? David J.

Re: Insert data if it is not existing

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

Re: Insert data if it is not existing

Tried it, but it still I am not inserting data into the table. On Thu, May 24, 2018 at 8:14 AM, tango ward wrote: > Oh yeah, my bad. I missed that FROM in SELECT. Sorry, i'll update the code > now. > > On Thu, May 24, 2018 at 8:04 AM, Adrian Klaver

Re: Insert data if it is not existing

Oh yeah, my bad. I missed that FROM in SELECT. Sorry, i'll update the code now. On Thu, May 24, 2018 at 8:04 AM, Adrian Klaver wrote: > On 05/23/2018 04:58 PM, tango ward wrote: > >> Thanks masters for responding again. >> >> I've tried running the code: >> >> INSERT

Re: Insert data if it is not existing

Sorry I forgot to mention. The table that I am working on right now doesn't have any unique column. AFAIK, I can only use ON CONFLICT if there's an error for unique column. On Thu, May 24, 2018 at 8:04 AM, Adrian Klaver wrote: > On 05/23/2018 04:58 PM, tango ward

Re: Insert data if it is not existing

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)) Sorry, I don't understand, where should I place the from clause? I just saw a sample code like this in SO, so I

Re: Insert data if it is not existing

On Wednesday, May 23, 2018, tango ward wrote: > Thanks masters for responding again. > > I've tried running the code: > > INSERT INTO my_table(name, age) > SELECT name, age > WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name) > > > this doesn't give me error but

Re: Insert data if it is not existing

Thanks masters for responding again. I've tried running the code: INSERT INTO my_table(name, age) SELECT name, age WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name) this doesn't give me error but it doesn't insert data either. On Thu, May 24, 2018 at 3:35 AM, Adrian Klaver

Re: Insert data if it is not existing

On Wednesday, May 23, 2018, tango ward wrote: > I just want to ask if it's possible to insert data if it's not existing > yet. > This seems more like a philosophical question than a technical one... ​but the answer is yes: CREATE TABLE test_t (a varchar, b varchar, c

RE: Insert data if it is not existing

Winfield Cc: pgsql-generallists.postgresql.org Subject: Re: Insert data if it is not existing thanks for the response Steven. Will ON CONFLICT DO UPDATE/NOTHING if there's no error? On Wed, May 23, 2018 at 5:43 PM, Steven Winfield <steven.winfi...@cantabcapital.com<mailto:steven

Re: Insert data if it is not existing

thanks for the response Steven. Will ON CONFLICT DO UPDATE/NOTHING if there's no error? On Wed, May 23, 2018 at 5:43 PM, Steven Winfield < steven.winfi...@cantabcapital.com> wrote: > INSERT … ON CONFLICT DO UPDATE … is probably what you want, perhaps using > a specified unique index/constraint:

RE: Insert data if it is not existing

INSERT … ON CONFLICT DO UPDATE … is probably what you want, perhaps using a specified unique index/constraint: https://www.postgresql.org/docs/10/static/sql-insert.html#SQL-ON-CONFLICT Steve. From: tango ward [mailto:tangowar...@gmail.com] Sent: 23 May 2018 10:04 To: