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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>> wrote:
On 05/23/2018 10:00 AM, David G. Johnston wrote:
On Wednesday, May 23, 2018, tango ward
<tangoward15@xxxxxxxxx <mailto:tangoward15@xxxxxxxxx>
<mailto:tangoward15@xxxxxxxxx
<mailto:tangoward15@xxxxxxxxx>> <mailto:tangoward15@xxxxxxxxx
<mailto:tangoward15@xxxxxxxxx>
<mailto:tangoward15@xxxxxxxxx
<mailto:tangoward15@xxxxxxxxx>>>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
