Re: Insert data if it is not existing

[Raymond O'Donnell]

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 as well:


insert into my_table(name, age)
select 'value 1', 'value 2'
where not exists (
  .
);

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

RE: Insert data if it is not existing

[Igor Neyman]

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>
Subject: Re: Insert data if it is not existing

On Thu, May 24, 2018 at 9:38 PM, Adrian Klaver 
<adrian.kla...@aklaver.com<mailto:adrian.kla...@aklaver.com>> wrote:
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 : 
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

I tried to figure how to make this work and could not, so I led you down a 
false path.




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

What I tried is
'''INSERT INTO my_table(name, age)
SELECT %s, %s,
WHERE NOT EXISTS(SELECT name FROM my_table WHERE name=%s)''', ('Scott', 23, 
'Scott'))

How about:

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

Regards,
Igor Neyman

Re: Insert data if it is not existing

[Adrian Klaver]

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 : 
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


I tried to figure how to make this work and could not, so I led you down 
a false path.









--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Insert data if it is not existing

[Adrian Klaver]

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 name=%s)''',
('Scott', 23)


I doubt that worked, you have three parameter markers(%s) and two
parameter values. Not only that two of the markers are for identifiers.


The count is indeed off but the two markers after the main select are 
literals, not identifiers.  As is the one being compared to name.


SELECT 'Scott', 23;

is a valid query.


Yeah, forgot about that.



David J.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Insert data if it is not existing

[David G. Johnston]

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
> parameter values. Not only that two of the markers are for identifiers.
>
>
The count is indeed off but the two markers after the main select are
literals, not identifiers.  As is the one being compared to name.

SELECT 'Scott', 23;

is a valid query.

David J.

Re: Insert data if it is not existing

[Adrian Klaver]

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 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.kla...@aklaver.com 


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


Please show the complete example that worked for completeness.

FYI, psql is your friend. When I work out queries I try them in psql 
first and then move up to whatever interface I will be using. This is 
usually done on a dev server so mistakes don't bring things down. If I 
have to work on a production instance then I do:

BEGIN;
some_query;
ROLLBACK;


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Insert data if it is not existing

[Adrian Klaver]

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:

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


INSERT doesn't have a where clause...

David J.



What I did is

'''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 
parameter values. Not only that two of the markers are for identifiers.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Insert data if it is not existing

[Adrian Klaver]

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 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.kla...@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.kla...@aklaver.com

Re: Insert data if it is not existing

[tango ward]

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 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.
>
>
>
Noted Sir. Thank you.

Re: Insert data if it is not existing

[David G. Johnston]

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 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.

Re: Insert data if it is not existing

[tango ward]

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 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.

Re: Insert data if it is not existing

[David G. Johnston]

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 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.

Re: Insert data if it is not existing

[tango ward]

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 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.kla...@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

Re: Insert data if it is not existing

[Adrian Klaver]

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

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

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




-- 
Adrian Klaver

adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Insert data if it is not existing

[David G. Johnston]

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

[Adrian Klaver]

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 
> wrote:


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 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.kla...@aklaver.com

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 <tangowar...@gmail.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.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>> wrote:
>>>
>>> On 05/23/2018 10:00 AM, David G. Johnston wrote:
>>>
>>> On Wednesday, May 23, 2018, tango ward <tangowar...@gmail.com
>>> <mailto:tangowar...@gmail.com> <mailto:tangowar...@gmail.com
>>> <mailto:tangowar...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>>
>>>
>>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>

Re: Insert data if it is not existing

[tango ward]

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.kla...@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.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 05/23/2018 10:00 AM, David G. Johnston wrote:
>>
>> On Wednesday, May 23, 2018, tango ward <tangowar...@gmail.com
>> <mailto:tangowar...@gmail.com> <mailto:tangowar...@gmail.com
>> <mailto:tangowar...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Insert data if it is not existing

[tango ward]

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.kla...@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.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 05/23/2018 10:00 AM, David G. Johnston wrote:
>>
>> On Wednesday, May 23, 2018, tango ward <tangowar...@gmail.com
>> <mailto:tangowar...@gmail.com> <mailto:tangowar...@gmail.com
>> <mailto:tangowar...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Insert data if it is not existing

[tango ward]

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

> 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 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.
>

Re: Insert data if it is not existing

[David G. Johnston]

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 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.

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

> On 05/23/2018 10:00 AM, David G. Johnston wrote:
>
>> On Wednesday, May 23, 2018, tango ward <tangowar...@gmail.com > tangowar...@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.kla...@aklaver.com
>

Re: Insert data if it is not existing

[David G. Johnston]

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

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:tangowar...@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.winfi...@cantabcapital.com<mailto:steven.winfi...@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<https://www.postgresql.org/docs/10/static/sql-insert.html#SQL-ON-CONFLICT>

Steve.


From: tango ward [mailto:tangowar...@gmail.com<mailto:tangowar...@gmail.com>]
Sent: 23 May 2018 10:04
To: pgsql-generallists.postgresql.org<http://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<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<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

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 <
steven.winfi...@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
>
>
>
> Steve.
>
>
>
>
> *From:* tango ward [mailto:tangowar...@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
> <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
> <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-- *
>

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:tangowar...@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.

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