Re: Insert data if it is not existing
От | Adrian Klaver |
---|---|
Тема | Re: Insert data if it is not existing |
Дата | |
Msg-id | 1a743474-5966-153e-997c-3b1dca5dd6b8@aklaver.com обсуждение исходный текст |
Ответ на | Re: Insert data if it is not existing (tango ward <tangoward15@gmail.com>) |
Список | 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
В списке pgsql-general по дате отправления: