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)