Re: insert - on conflict question

[Beena Emerson <memissemerson@xxxxxxxxx>]

On Wed, Feb 1, 2017 at 4:15 PM, Johann Spies <johann.spies@xxxxxxxxx> wrote:

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;

In the  ON CONFLICT... SET we need to use EXCLUDED keyword.

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

 

--

Thank you, 

Beena Emerson

Have a Great Day!