[GENERAL] insert - on conflict question

Поиск
Список
Период
Сортировка
От Johann Spies
Тема [GENERAL] insert - on conflict question
Дата
Msg-id CAGZ55DQL0SnNDPg0Ovp5k3fw9vHi5uKmd0O_x1ObtORXYOC=vA@mail.gmail.com
обсуждение исходный текст
Ответы Re: [GENERAL] insert - on conflict question  (Beena Emerson <memissemerson@gmail.com>)
Список pgsql-general
How do I formulate the on conflict  do update-section of this query? When I try set title=q.title, q is unknown.  When I try and change 'title' in the select-part to something else and try title=ti I get the message that ti cannot be used in this part of the query.

INSERT INTO wos_2017_1.article (ut,   title,   author_count)
WITH p AS (   SELECT       ARRAY [ ARRAY [ 't', 'some_namespace' ] ] AS ns),
q AS (   SELECT       ut,       unnest (xpath ('//t:title[@type= "item"]/text()',               xml,               p.ns))::text title,       unnest (xpath ('//t:summary/t:names/@count',               xml,               p.ns))::TEXT::INTEGER AS author_count   FROM       p,       source.cover_2016)
SELECT   ut,   regexp_replace (regexp_replace (regexp_replace (title, '<', '<', 'g'), '&', '&', 'g'), '>', '>', 'g')   title,   author_count
FROM   q 

ON CONFLICT (ut) DO UPDATE SET title = title, author_count = author_count;


--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: [GENERAL] Making changes to PostgreSQL's configure logic so as to have contrib modules installed in a specific directory and make them use PGXS?
Следующее
От: Beena Emerson
Дата:
Сообщение: Re: [GENERAL] insert - on conflict question