From: | Beena Emerson <memissemerson(at)gmail(dot)com> |
---|---|
To: | Johann Spies <johann(dot)spies(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: insert - on conflict question |
Date: | 2017-02-01 11:33:32 |
Message-ID: | CAOG9ApGaVErG8gZrbesucGGEWPBLgbQR4JR18MwSZ3844ahkKw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Feb 1, 2017 at 4:15 PM, Johann Spies <johann(dot)spies(at)gmail(dot)com> 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[(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;
>
>
>
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2017-02-01 12:16:28 | Re: Can we not give tyrannical pedants control of #postgresql? |
Previous Message | Johann Spies | 2017-02-01 10:45:31 | insert - on conflict question |