Insert data if it is not existing

classic Classic list List threaded Threaded
33 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Insert data if it is not existing

tango ward

Hi,

I just want to ask if it's possible to insert data if it's not existing yet. I was able to play around with UPSERT before but that was when there was an error for duplicate data. In my scenario, no error message is showing.


Any suggestion?


Thanks,
J
Reply | Threaded
Open this post in threaded view
|

RE: Insert data if it is not existing

Steven Winfield

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:[hidden email]]
Sent: 23 May 2018 10:04
To: pgsql-generallists.postgresql.org
Subject: Insert data if it is not existing

 

 

Hi,

 

I just want to ask if it's possible to insert data if it's not existing yet. I was able to play around with UPSERT before but that was when there was an error for duplicate data. In my scenario, no error message is showing.

 

 

Any suggestion?

 

 

Thanks,

J



This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you.

GAM Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and use information about you in the course of your interactions with us. Full details about the data types we collect and what we use this for and your related rights is set out in our online privacy policy at https://www.gam.com/en/legal/privacy-policy. Please familiarise yourself with this policy and check it from time to time for updates as it supplements this notice
Reply | Threaded
Open this post in threaded view
|

Re: Insert data if it is not existing

tango ward
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 <[hidden email]> wrote:

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:[hidden email]]
Sent: 23 May 2018 10:04
To: pgsql-generallists.postgresql.org
Subject: Insert data if it is not existing

 

 

Hi,

 

I just want to ask if it's possible to insert data if it's not existing yet. I was able to play around with UPSERT before but that was when there was an error for duplicate data. In my scenario, no error message is showing.

 

 

Any suggestion?

 

 

Thanks,

J



This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you.

GAM Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and use information about you in the course of your interactions with us. Full details about the data types we collect and what we use this for and your related rights is set out in our online privacy policy at https://www.gam.com/en/legal/privacy-policy. Please familiarise yourself with this policy and check it from time to time for updates as it supplements this notice

Reply | Threaded
Open this post in threaded view
|

RE: Insert data if it is not existing

Steven Winfield

From the docs:

“ON CONFLICT can be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error.”

 

So if the INSERT part succeeds then the ON CONFLICT part is never executed.

If the INSERT fails with due to a violation of the constraint you specified (or was implied) then the ON CONFLICT part is executed instead. An UPDATE here can raise further errors, of course.

If the INSERT fails for a different reason then the ON CONFLICT part is not executed.

 

Steve.

 

From: tango ward [mailto:[hidden email]]
Sent: 23 May 2018 10:46
To: Steven 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 <[hidden email]> wrote:

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:[hidden email]]
Sent: 23 May 2018 10:04
To: pgsql-generallists.postgresql.org
Subject: Insert data if it is not existing

 

 

Hi,

 

I just want to ask if it's possible to insert data if it's not existing yet. I was able to play around with UPSERT before but that was when there was an error for duplicate data. In my scenario, no error message is showing.

 

 

Any suggestion?

 

 

Thanks,

J

 


This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you.

GAM Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and use information about you in the course of your interactions with us. Full details about the data types we collect and what we use this for and your related rights is set out in our online privacy policy at https://www.gam.com/en/legal/privacy-policy. Please familiarise yourself with this policy and check it from time to time for updates as it supplements this notice


 

Reply | Threaded
Open this post in threaded view
|

Re: Insert data if it is not existing

David G Johnston
In reply to this post by tango ward
On Wednesday, May 23, 2018, tango ward <[hidden email]> 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 integer);
INSERT INTO test_t
SELECT '1', '2', 3 WHERE false;​ --where false causes the data to effectively "not exist"

As for ON CONFLICT: conflicts can only happen between things that exist.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Insert data if it is not existing

Adrian Klaver-4
On 05/23/2018 10:00 AM, David G. Johnston wrote:

> On Wednesday, May 23, 2018, tango ward <[hidden email]
> <mailto:[hidden email]>> 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 integer);
> INSERT INTO test_t
> SELECT '1', '2', 3 WHERE false;​ --where false causes the data to
> effectively "not exist"
>
> As for ON CONFLICT: conflicts can only happen between things that exist.

Well that made my day:)

>
> David J.
>


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Insert data if it is not existing

tango ward
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 <[hidden email]> wrote:
On 05/23/2018 10:00 AM, David G. Johnston wrote:
On Wednesday, May 23, 2018, tango ward <[hidden email] <mailto:[hidden email]>> 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 integer);
INSERT INTO test_t
SELECT '1', '2', 3 WHERE false;​ --where false causes the data to effectively "not exist"

As for ON CONFLICT: conflicts can only happen between things that exist.

Well that made my day:)


David J.



--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Insert data if it is not existing

David G Johnston
On Wednesday, May 23, 2018, tango ward <[hidden email]> 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 it doesn't insert data either.


I'm doubting your assertion that it doesn't error.   How do you run that query such that age and name are recognized given the main query doesn't have a from clause?

David J.
Reply | Threaded
Open this post in threaded view
|

Re: Insert data if it is not existing

Adrian Klaver-4
In reply to this post by tango ward
On 05/23/2018 04:58 PM, 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)

The first thing I see is that:

SELECT name, age

is not being selected from anywhere, for example:

SELECT name, age FROM some_table.

The second thing I see is why not use ON CONFLICT?

>
>
> this doesn't give me error but it doesn't insert data either.
>
> On Thu, May 24, 2018 at 3:35 AM, Adrian Klaver
> <[hidden email] <mailto:[hidden email]>> wrote:
>
>     On 05/23/2018 10:00 AM, David G. Johnston wrote:
>
>         On Wednesday, May 23, 2018, tango ward <[hidden email]
>         <mailto:[hidden email]> <mailto:[hidden email]
>         <mailto:[hidden email]>>> 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 integer);
>         INSERT INTO test_t
>         SELECT '1', '2', 3 WHERE false;​ --where false causes the data
>         to effectively "not exist"
>
>         As for ON CONFLICT: conflicts can only happen between things
>         that exist.
>
>
>     Well that made my day:)
>
>
>         David J.
>
>
>
>     --
>     Adrian Klaver
>     [hidden email] <mailto:[hidden email]>
>
>


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Insert data if it is not existing

tango ward
In reply to this post by David G Johnston
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 gave it a shot


On Thu, May 24, 2018 at 8:04 AM, David G. Johnston <[hidden email]> wrote:
On Wednesday, May 23, 2018, tango ward <[hidden email]> 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 it doesn't insert data either.


I'm doubting your assertion that it doesn't error.   How do you run that query such that age and name are recognized given the main query doesn't have a from clause?

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Insert data if it is not existing

tango ward
In reply to this post by Adrian Klaver-4
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 <[hidden email]> wrote:
On 05/23/2018 04:58 PM, 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)

The first thing I see is that:

SELECT name, age

is not being selected from anywhere, for example:

SELECT name, age FROM some_table.

The second thing I see is why not use ON CONFLICT?



this doesn't give me error but it doesn't insert data either.

On Thu, May 24, 2018 at 3:35 AM, Adrian Klaver <[hidden email] <mailto:[hidden email]>> wrote:

    On 05/23/2018 10:00 AM, David G. Johnston wrote:

        On Wednesday, May 23, 2018, tango ward <[hidden email]
        <mailto:[hidden email]> <mailto:[hidden email]
        <mailto:[hidden email]>>> 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 integer);
        INSERT INTO test_t
        SELECT '1', '2', 3 WHERE false;​ --where false causes the data
        to effectively "not exist"

        As for ON CONFLICT: conflicts can only happen between things
        that exist.


    Well that made my day:)


        David J.



    --     Adrian Klaver
    [hidden email] <mailto:[hidden email]>




--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Insert data if it is not existing

tango ward
In reply to this post by Adrian Klaver-4
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 <[hidden email]> wrote:
On 05/23/2018 04:58 PM, 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)

The first thing I see is that:

SELECT name, age

is not being selected from anywhere, for example:

SELECT name, age FROM some_table.

The second thing I see is why not use ON CONFLICT?



this doesn't give me error but it doesn't insert data either.

On Thu, May 24, 2018 at 3:35 AM, Adrian Klaver <[hidden email] <mailto:[hidden email]>> wrote:

    On 05/23/2018 10:00 AM, David G. Johnston wrote:

        On Wednesday, May 23, 2018, tango ward <[hidden email]
        <mailto:[hidden email]> <mailto:[hidden email]
        <mailto:[hidden email]>>> 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 integer);
        INSERT INTO test_t
        SELECT '1', '2', 3 WHERE false;​ --where false causes the data
        to effectively "not exist"

        As for ON CONFLICT: conflicts can only happen between things
        that exist.


    Well that made my day:)


        David J.



    --     Adrian Klaver
    [hidden email] <mailto:[hidden email]>




--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Insert data if it is not existing

tango ward
Tried it, but it still I am not inserting data into the table.

On Thu, May 24, 2018 at 8:14 AM, tango ward <[hidden email]> 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 <[hidden email]> wrote:
On 05/23/2018 04:58 PM, 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)

The first thing I see is that:

SELECT name, age

is not being selected from anywhere, for example:

SELECT name, age FROM some_table.

The second thing I see is why not use ON CONFLICT?



this doesn't give me error but it doesn't insert data either.

On Thu, May 24, 2018 at 3:35 AM, Adrian Klaver <[hidden email] <mailto:[hidden email]>> wrote:

    On 05/23/2018 10:00 AM, David G. Johnston wrote:

        On Wednesday, May 23, 2018, tango ward <[hidden email]
        <mailto:[hidden email]> <mailto:[hidden email]
        <mailto:[hidden email]>>> 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 integer);
        INSERT INTO test_t
        SELECT '1', '2', 3 WHERE false;​ --where false causes the data
        to effectively "not exist"

        As for ON CONFLICT: conflicts can only happen between things
        that exist.


    Well that made my day:)


        David J.



    --     Adrian Klaver
    [hidden email] <mailto:[hidden email]>




--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Insert data if it is not existing

Adrian Klaver-4
In reply to this post by tango ward
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})

>
>
> On Thu, May 24, 2018 at 8:04 AM, David G. Johnston
> <[hidden email] <mailto:[hidden email]>> wrote:
>
>     On Wednesday, May 23, 2018, tango ward <[hidden email]
>     <mailto:[hidden email]>> 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 it doesn't insert data either.
>
>
>     I'm doubting your assertion that it doesn't error.   How do you run
>     that query such that age and name are recognized given the main
>     query doesn't have a from clause?
>
>     David J.
>
>


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Insert data if it is not existing

David G Johnston
In reply to this post by tango ward
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 <[hidden email]> wrote:
Tried it, but it still I am not inserting data into the table.

tried what?

David J.
Reply | Threaded
Open this post in threaded view
|

Re: Insert data if it is not existing

Adrian Klaver-4
In reply to this post by tango ward
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:

https://www.postgresql.org/docs/10/static/sql-insert.html#SQL-ON-CONFLICT

"index_expression

     Similar to index_column_name, but used to infer expressions on
table_name columns appearing within index definitions (not simple
columns). Follows CREATE INDEX format. SELECT privilege on any column
appearing within index_expression is required.
"

I take this to mean something like:

ON CONFLICT UNIQUE INDEX name_idx ON my_table(name)


>
> On Thu, May 24, 2018 at 8:04 AM, Adrian Klaver
> <[hidden email] <mailto:[hidden email]>> wrote:
>
>     On 05/23/2018 04:58 PM, 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)
>
>
>     The first thing I see is that:
>
>     SELECT name, age
>
>     is not being selected from anywhere, for example:
>
>     SELECT name, age FROM some_table.
>
>     The second thing I see is why not use ON CONFLICT?
>
>
>
>         this doesn't give me error but it doesn't insert data either.
>
>         On Thu, May 24, 2018 at 3:35 AM, Adrian Klaver
>         <[hidden email] <mailto:[hidden email]>
>         <mailto:[hidden email]
>         <mailto:[hidden email]>>> wrote:
>
>              On 05/23/2018 10:00 AM, David G. Johnston wrote:
>
>                  On Wednesday, May 23, 2018, tango ward
>         <[hidden email] <mailto:[hidden email]>
>                  <mailto:[hidden email]
>         <mailto:[hidden email]>> <mailto:[hidden email]
>         <mailto:[hidden email]>
>                  <mailto:[hidden email]
>         <mailto:[hidden email]>>>> 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 integer);
>                  INSERT INTO test_t
>                  SELECT '1', '2', 3 WHERE false;​ --where false causes
>         the data
>                  to effectively "not exist"
>
>                  As for ON CONFLICT: conflicts can only happen between
>         things
>                  that exist.
>
>
>              Well that made my day:)
>
>
>                  David J.
>
>
>
>              --     Adrian Klaver
>         [hidden email] <mailto:[hidden email]>
>         <mailto:[hidden email]
>         <mailto:[hidden email]>>
>
>
>
>
>     --
>     Adrian Klaver
>     [hidden email] <mailto:[hidden email]>
>
>


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Insert data if it is not existing

tango ward
In reply to this post by Adrian Klaver-4

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



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



Reply | Threaded
Open this post in threaded view
|

Re: Insert data if it is not existing

David G Johnston
On Wednesday, May 23, 2018, tango ward <[hidden email]> 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 is at least one record in my_table the exists returns true, and the not inverts it to false and the main select returns zero rows.  You have successfully inserted a record that doesn't exist (i.e., you've inserted nothing just like you observe).

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Insert data if it is not existing

tango ward

On Thu, May 24, 2018 at 9:09 AM, David G. Johnston <[hidden email]> wrote:
On Wednesday, May 23, 2018, tango ward <[hidden email]> 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 is at least one record in my_table the exists returns true, and the not inverts it to false and the main select returns zero rows.  You have successfully inserted a record that doesn't exist (i.e., you've inserted nothing just like you observe).

David J.

Any advice on this Sir? Even adding the FROM statement in SELECT statement doesn't insert the data


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

I also need to perform the same task but on another table but the data for that is from another DB.
Reply | Threaded
Open this post in threaded view
|

Re: Insert data if it is not existing

David G Johnston
On Wednesday, May 23, 2018, tango ward <[hidden email]> wrote:

On Thu, May 24, 2018 at 9:09 AM, David G. Johnston <[hidden email]> wrote:
On Wednesday, May 23, 2018, tango ward <[hidden email]> 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 is at least one record in my_table the exists returns true, and the not inverts it to false and the main select returns zero rows.  You have successfully inserted a record that doesn't exist (i.e., you've inserted nothing just like you observe).

David J.

Any advice on this Sir? Even adding the FROM statement in SELECT statement doesn't insert the data


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

I also need to perform the same task but on another table but the data for that is from another DB.

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 knowledge of the various parts that will make up the solution, and have been given more in the rest of this thread, and figuring out how they go together is something you will either get, or not.

Or wait for a less harsh person to give you the answer and move you forward to the next beginner's problem.

David J.

12