Re: Insert data if it is not existing

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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>> wrote:
>
>     On 05/23/2018 10:00 AM, David G. Johnston wrote:
>
>         On Wednesday, May 23, 2018, tango ward
> <tangoward15(at)gmail(dot)com <mailto:tangoward15(at)gmail(dot)com>
>         <mailto:tangoward15(at)gmail(dot)com
> <mailto:tangoward15(at)gmail(dot)com>> <mailto:tangoward15(at)gmail(dot)com
> <mailto:tangoward15(at)gmail(dot)com>
>         <mailto:tangoward15(at)gmail(dot)com
> <mailto:tangoward15(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
>
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com