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:tangoward15@gmail.com]
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.

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.Winfield@cantabcapital.com> wrote:

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:tangoward15@gmail.com]
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 <Steven.Winfield@cantabcapital.com<mailto:Steven.Winfield@cantabcapital.com>> 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&lt;https://www.postgresql.org/docs/10/static/sql-insert.html#SQL-ON-CONFLICT&gt;

Steve.

From: tango ward [mailto:tangoward15@gmail.com<mailto:tangoward15@gmail.com>]
Sent: 23 May 2018 10:04
To: pgsql-generallists.postgresql.org<http://pgsql-generallists.postgresql.org&gt;
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&lt;http://www.gam.com/en/Legal/Email+disclosures+EU&gt; 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&lt;https://www.gam.com/en/legal/privacy-policy&gt;. Please familiarise yourself with this policy and check it from time to time for updates as it supplements this notice
________________________________

On Wednesday, May 23, 2018, tango ward <tangoward15@gmail.com> 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.

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

On Wednesday, May 23, 2018, tango ward <tangoward15@gmail.com
<mailto:tangoward15@gmail.com>> 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
adrian.klaver@aklaver.com

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 <adrian.klaver@aklaver.com>
wrote:

On Wednesday, May 23, 2018, tango ward <tangoward15@gmail.com> 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.

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
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

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

On Wednesday, May 23, 2018, tango ward <tangoward15@gmail.com
<mailto:tangoward15@gmail.com> <mailto:tangoward15@gmail.com
<mailto:tangoward15@gmail.com>>> 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
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

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 <
david.g.johnston@gmail.com> 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.

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

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

On Thu, May 24, 2018 at 8:14 AM, tango ward <tangoward15@gmail.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})

On Thu, May 24, 2018 at 8:04 AM, David G. Johnston
<david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:

On Wednesday, May 23, 2018, tango ward <tangoward15@gmail.com
<mailto:tangoward15@gmail.com>> 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
adrian.klaver@aklaver.com

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 <tangoward15@gmail.com> wrote:

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

tried what?

David J.

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
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> 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
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>> wrote:

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

        On Wednesday, May 23, 2018, tango ward
<tangoward15@gmail.com <mailto:tangoward15@gmail.com>
        <mailto:tangoward15@gmail.com
<mailto:tangoward15@gmail.com>> <mailto:tangoward15@gmail.com
<mailto:tangoward15@gmail.com>
        <mailto:tangoward15@gmail.com
<mailto:tangoward15@gmail.com>>>> 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
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

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

On Wednesday, May 23, 2018, tango ward <tangoward15@gmail.com> 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.

On Thu, May 24, 2018 at 9:09 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Wednesday, May 23, 2018, tango ward <tangoward15@gmail.com> 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.

On Wednesday, May 23, 2018, tango ward <tangoward15@gmail.com> wrote:

On Thu, May 24, 2018 at 9:09 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Wednesday, May 23, 2018, tango ward <tangoward15@gmail.com> 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.