CODEWITHSUNDEEP
postgresqlupsert
All3
All3

Reputation: 95

PostgreSQL UPDATE ON CONFLICT only under some condition

I've got a table like this

create table public.incominglog
(
    envelopeid        varchar(36) default ''::character varying not null,
    messagetype       text        default NULL::character varying,
    initialenvelopeid varchar(36) default NULL::character varying,
    processid         varchar(36) default NULL::character varying,
    logtimestamp      timestamp,
    customscode       varchar(8),
    exchtypeid        integer
);
create unique index incominglog_envelopeid_uindex
    on public.incominglog (envelopeid);
create unique index incominglog_initialenvelopeid_uindex
    on public.incominglog (initialenvelopeid);

and I'm trying to insert values like this

INSERT INTO
    incominglog (
        envelopeid,
        messagetype,
        initialenvelopeid,
        processid,
        logtimestamp,
        customscode,
        exchtypeid
    )
VALUES
    (
        'ae2a2b46-ae4f-42a1-ada3-1f8f0aff7361',
        'CMN.00001',
        'aad06a96-667f-42c9-9196-8e0fec103d8b',
        '4fed3854-e1de-42eb-b2c7-3ad714b58a9e',
        '2019-04-17 14:57:54.195588',
        '10210130',
        '19200'
    ) 
ON CONFLICT (initialenvelopeid) 
DO update set
    envelopeid = excluded.envelopeid,
    messagetype = excluded.messagetype,
    logtimestamp = excluded.logtimestamp

Help me please make changes in my insert script that it won't update values if there is already 'CMN.00001' in messagetype with the same initialenvelopeid.

Upvotes: 3

Views: 779

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246033

You can add a WHERE clause to ON CONFLICT:

ON CONFLICT DO UPDATE ...
   WHERE incominglog.messagetype <> 'CMN.00001'

Upvotes: 5

Related Questions