Postgresql 11.1

I have a somewhat thorny problem. Assume I have a standard person file that looks similar to this:

```
CREATE TABLE patients
(
    recid integer NOT NULL DEFAULT nextval('patients_recid_seq'::regclass),
    lastname text COLLATE pg_catalog."default" NOT NULL,
    firstname text COLLATE pg_catalog."default" NOT NULL,
    mi text COLLATE pg_catalog."default",
    birthdate date NOT NULL,
    newrecid integer,
    CONSTRAINT pk_patients PRIMARY KEY (recid)
)

```

As this is a medical database, I have related diagnosis file something like this:

```
CREATE TABLE dx
(
    recid integer NOT NULL DEFAULT nextval('dx_recid_seq'::regclass),
    tservice timestamp without time zone NOT NULL,
    patient_recid integer NOT NULL,
    disease_recid integer NOT NULL,
    CONSTRAINT pk_dx_recid PRIMARY KEY (recid),
    CONSTRAINT dx_disease_recid FOREIGN KEY (disease_recid)
        REFERENCES phoenix.disease (recid) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT dx_patient_fk FOREIGN KEY (patient_recid)
        REFERENCES phoenix.patients (recid) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT
)

```

The "newrecid" is for the situation where the patient changes their name. i.e., it is a forward pointer to the correct account with the correct name. (I didn't think it wise to delete the wrong name as many tables and other records are related to the primary key in patients.)

Through the use of this recursion, all primary keys for the patient can be easily found:

```
WITH RECURSIVE  readtoend AS (   -- MUST get all the recid's for the patient.
            SELECT recid, newrecid
                FROM patients p1
                WHERE p1.recid = n.patient_recid
            UNION
            SELECT c.recid, c.newrecid
                FROM patients c
                INNER JOIN readtoend s ON s.newrecid = c.recid  
            ),
            readtostart AS (
                SELECT recid, newrecid
                        FROM patients p1
                        WHERE p1.recid = n.patient_recid
                    UNION
                    SELECT c.recid, c.newrecid
                        FROM patients c
                        INNER JOIN readtostart s ON s.recid = c.newrecid                
            )
            SELECT recid FROM readtoend 
            UNION
            SELECT recid FROM readtostart

```

where n.patient\_recid is any of the primary keys the patient/person may have used.

Now, the problem: In order to allow a unique index based on the date of service, the diagnosis file has a unique index of:

```
UNIQUE INDEX idx_unique_dx
ON phoenix.dx USING btree
(patient_recid ASC NULLS LAST, (tservice::date) ASC NULLS LAST, disease_recid ASC NULLS LAST)
TABLESPACE pg_default;

```

I wish to keep the unique index in order to avoid duplicates.

The problem: Although the unique index prevents duplication of patient, date of service, and disease, if the person (with the same recid) and the same date of service has both the "old" disease and the "new" disease, and the "old" disease is updated to the new one, the unique constraint will obviously be violated. i.e., this fails when both old and new diseases already exist for the patient:

**ERROR: duplicate key value violates unique constraint "idx\_unique\_dx"
DETAIL: Key (patient\_recid, (tservice::date), disease\_recid)=(15478, 2019-11-14, 17287) already exists.**

```
WITH _in (tservice, patient_recid, disease_recid, new_disease_recid) AS (
    VALUES ('2021-04-18'::timestamp, '15478'::integer, '17242'::integer,'17287'::integer)
)   
UPDATE dx d
SET disease_recid = n.new_disease_recid
FROM _in n
LEFT JOIN LATERAL ( WITH RECURSIVE  readtoend AS (   -- MUST get all the recid's for the patient.
                SELECT recid, newrecid
                    FROM patients p1
                    WHERE p1.recid = n.patient_recid
                UNION
                SELECT c.recid, c.newrecid
                    FROM patients c
                    INNER JOIN readtoend s ON s.newrecid = c.recid  
                ),
                readtostart AS (
                    SELECT recid, newrecid
                            FROM patients p1
                            WHERE p1.recid = n.patient_recid
                        UNION
                        SELECT c.recid, c.newrecid
                            FROM patients c
                            INNER JOIN readtostart s ON s.recid = c.newrecid                
                )
                SELECT recid FROM readtoend 
                UNION
                SELECT recid FROM readtostart        
             )  u ON true
WHERE d.patient_recid = u.recid AND d.disease_recid = n.disease_recid AND d.tservice::date <= n.tservice::date 

```

What I need (in concept) is something like:

```
Update dx
set <column name> = new value
from (subquery)
ON CONFLICT DO DELETE

```

That is, if updating a record would cause it to conflict with another record, then I need the record (before update) to be deleted.

Can this be done? if so, how?

Thanks for any help or suggestions!

TIA

Edit #1: This seems to work.
Question: **Will the delete cte ALWAYS be done before the Update?**

```
WITH RECURSIVE _in (tservice, patient_recid, disease_recid, new_disease_recid) AS (
        VALUES ('2021-04-18'::timestamp, '15478'::integer, '17242'::integer, '17287'::integer)  --15478  '23569'
),  
 readtoend AS (   
                SELECT n.patient_recid, recid, newrecid
                    FROM patients p1
                    JOIN _in n ON p1.recid = n.patient_recid
                UNION
                SELECT s.patient_recid, c.recid, c.newrecid
                    FROM patients c
                    INNER JOIN readtoend s ON s.newrecid = c.recid  
),
readtostart AS (
    SELECT n.patient_recid, recid, newrecid
            FROM patients p1
            JOIN _in n ON p1.recid = n.patient_recid
        UNION
        SELECT s.patient_recid, c.recid, c.newrecid
            FROM patients c
            INNER JOIN readtostart s ON s.recid = c.newrecid                
),
_all_patient_id AS (
    SELECT patient_recid, recid FROM readtoend 
    UNION
    SELECT patient_recid, recid FROM readtostart
),
_get_existing_target AS (
    SELECT d.* 
    FROM _in n
    JOIN _all_patient_id j ON (n.patient_recid = j.patient_recid)
    JOIN dx d ON (d.patient_recid, d.disease_recid) = (j.recid, n.new_disease_recid )
    WHERE d.tservice <= n.tservice
),
_get_conflicts AS (
    SELECT d.* 
    FROM _in n
    JOIN _all_patient_id j ON (n.patient_recid = j.patient_recid)
    JOIN dx d ON (d.patient_recid, d.disease_recid) = (j.recid, n.disease_recid)
    WHERE EXISTS (SELECT 1 
                  FROM _get_existing_target x
                  WHERE d.patient_recid = x.patient_recid AND d.tservice::date = x.tservice::date)
),
_delete AS (
    DELETE FROM dx d
    USING _get_conflicts f
    WHERE d.recid = f.recid
    RETURNING *
)
UPDATE dx d
SET disease_recid = n.new_disease_recid
FROM _in n
JOIN _all_patient_id j ON (j.patient_recid = n.patient_recid)
WHERE d.patient_recid = j.recid AND d.disease_recid = n.new_disease_recid AND d.tservice::date <= n.tservice::date

```