insert - on conflict question

From: Johann Spies <johann(dot)spies(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: insert - on conflict question
Date: 2017-02-01 10:45:31
Message-ID: CAGZ55DQL0SnNDPg0Ovp5k3fw9vHi5uKmd0O_x1ObtORXYOC=vA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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[(at)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_countFROM
q

ON CONFLICT (ut)
DO UPDATESET
title = title,
author_count = author_count;

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Beena Emerson 2017-02-01 11:33:32 Re: insert - on conflict question
Previous Message Tom Lane 2017-02-01 03:19:40 Re: Making changes to PostgreSQL's configure logic so as to have contrib modules installed in a specific directory and make them use PGXS?