Re: Insert data if it is not existing - Mailing list pgsql-general

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