Thread: Insert data if it is not existing

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

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

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

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

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

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 05/23/2018 06:03 PM, tango ward wrote:
> 
> On Thu, May 24, 2018 at 8:19 AM, Adrian Klaver 
> <adrian.klaver@aklaver.com <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@aklaver.com <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@aklaver.com

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

On 05/23/2018 08:04 PM, tango ward wrote:
> 
> On Thu, May 24, 2018 at 10:55 AM, David G. Johnston 
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
> 
>     On Wednesday, May 23, 2018, Adrian Klaver <adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>> 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.klaver@aklaver.com

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

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

On 05/23/2018 09:39 PM, David G. Johnston wrote:
> On Wednesday, May 23, 2018, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> 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.klaver@aklaver.com

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

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
rod@iol.ie