How to solve this complex insertion problem In our database we store policycase records. A policycase has a 1:1 relation to a (requested) policy record. The policy record has a 1:n relationship to policy_map. Where policy_map is a key, value table.

When a policycase is changed from our user application, the complete policycase record and all it's child records are copied and stored again. At least, that was the requirement. Due to a bug only a small number of the policy_map records are copied to the new policycase.

We want to execute an insert query that creates the missing policy_map records for the copied record, when they do not exist.

The old policycase can be recognized from a status HISTORICAL and the copied policycase from a status ARCHIVED. Both policycases have the same uniqueid field.

So the following select query gives me the policy_map records for the historical and the archived (copied) policycase records for a certain uniqueid:

SELECT 
    policycase.status,
    policy.id,
    policy.uniqueid, 
    policy_map.map_key, 
    policy_map.map_value 
FROM policycase 
INNER JOIN policy ON policy.id = policycase.requested_id
INNER JOIN policy_map ON policy_map.policy_id = policy.id
WHERE policy.uniqueid = '91157235'


The result of this query is something like:

+------------+--------+----------+--------------------------------+-----------+
|   status   |   id   | uniqueid |            map_key             | map_value |
+------------+--------+----------+--------------------------------+-----------+
| HISTORICAL | 438957 | 91157235 | isPropertyOwner                | TRUE      |
| HISTORICAL | 438957 | 91157235 | livingAtProperty               | TRUE      |
| HISTORICAL | 438957 | 91157235 | stateOfMaintenance             | GOED      |
| HISTORICAL | 438957 | 91157235 | stateOfMaintenanceRoof         | 0         |
| HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseContent | J         |
| HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseFire    | J         |
| ARCHIVED   | 451423 | 91157235 | isPropertyOwner                | TRUE      |
| ARCHIVED   | 451423 | 91157235 | livingAtProperty               | TRUE      |
| ARCHIVED   | 451423 | 91157235 | stateOfMaintenance             | GOED      |
+------------+--------+----------+--------------------------------+-----------+


In the table above, the map records


stateOfMaintenanceRoof
unsecuredGuaranteeHouseContent
unsecuredGuaranteeHouseFire


need to be copied to the policy_map table, related to the ARCHIVED policycase. That needs to be done for every uniqueid in the database.

How would a query or function that accomplishes this look?

We are running Postgres 9.6 on Amazon RDS.
Setup:

create table policycase
(requested_id int
,status       varchar(30));

insert into policycase values (438957,'HISTORICAL');
insert into policycase values (451423,'ARCHIVED');

create table policy
(id           int
,uniqueid     varchar(10));

insert into policy values (438957, '91157235');
insert into policy values (451423, '91157235');

create table policy_map
(policy_id    int
,map_key      varchar(100)
,map_value    varchar(30));

insert into policy_map values (438957, 'isPropertyOwner'               , 'TRUE');
insert into policy_map values (438957, 'livingAtProperty'              , 'TRUE');
insert into policy_map values (438957, 'stateOfMaintenance'            , 'GOED');
insert into policy_map values (438957, 'stateOfMaintenanceRoof'        , '0');
insert into policy_map values (438957, 'unsecuredGuaranteeHouseContent', 'J');
insert into policy_map values (438957, 'unsecuredGuaranteeHouseFire'   , 'J');

insert into policy_map values (451423, 'isPropertyOwner'               , 'TRUE');
insert into policy_map values (451423, 'livingAtProperty'              , 'TRUE');
insert into policy_map values (451423, 'stateOfMaintenance'            , 'GOED');


Before insert:

SELECT 
    policycase.status,
    policy.id,
    policy.uniqueid, 
    policy_map.map_key, 
    policy_map.map_value 
FROM policycase 
INNER JOIN policy ON policy.id = policycase.requested_id
INNER JOIN policy_map ON policy_map.policy_id = policy.id
WHERE policy.uniqueid = '91157235'
order by policy.uniqueid,policy.id,policy_map.map_key;



status     |     id | uniqueid | map_key                        | map_value
---------- | ------ | -------- | ------------------------------ | ---------
HISTORICAL | 438957 | 91157235 | isPropertyOwner                | TRUE     
HISTORICAL | 438957 | 91157235 | livingAtProperty               | TRUE     
HISTORICAL | 438957 | 91157235 | stateOfMaintenance             | GOED     
HISTORICAL | 438957 | 91157235 | stateOfMaintenanceRoof         | 0        
HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseContent | J        
HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseFire    | J        
ARCHIVED   | 451423 | 91157235 | isPropertyOwner                | TRUE     
ARCHIVED   | 451423 | 91157235 | livingAtProperty               | TRUE     
ARCHIVED   | 451423 | 91157235 | stateOfMaintenance             | GOED        


The proposed INSERT:

insert into policy_map

select p2.id,
       pm1.map_key,
       pm1.map_value

from   policycase pc1
join   policy p1
on     pc1.requested_id = p1.id

join   policy_map pm1
on     pm1.policy_id = p1.id

-- join with p2/pc2 to obtain id for ARCHIVED rows
join   policy p2
on     p2.uniqueid  = p1.uniqueid

join   policycase pc2
on     pc2.requested_id = p2.id

where  p1.uniqueid = '91157235'
and    pc1.status  = 'HISTORICAL'
and    pc2.status  = 'ARCHIVED'

and    not exists(select 1
                  from   policycase pcx
                  join   policy px
                  on     pcx.requested_id = px.id

                  join   policy_map pmx
                  on     pmx.policy_id    = px.id

                  where  px.uniqueid      = p1.uniqueid
                  and    pcx.status       = 'ARCHIVED'

                  and    pmx.map_key      = pm1.map_key
                  and    pmx.map_value    = pm1.map_value);


NOTE: There may be a way to eliminate some of the joins but I'll leave it like this for now as it's easier (for me) to understand what's going oin.

After insert:

SELECT 
    policycase.status,
    policy.id,
    policy.uniqueid, 
    policy_map.map_key, 
    policy_map.map_value 
FROM policycase 
INNER JOIN policy ON policy.id = policycase.requested_id
INNER JOIN policy_map ON policy_map.policy_id = policy.id
WHERE policy.uniqueid = '91157235'
order by policy.uniqueid,policy.id,policy_map.map_key;



status     |     id | uniqueid | map_key                        | map_value
---------- | ------ | -------- | ------------------------------ | ---------
HISTORICAL | 438957 | 91157235 | isPropertyOwner                | TRUE     
HISTORICAL | 438957 | 91157235 | livingAtProperty               | TRUE     
HISTORICAL | 438957 | 91157235 | stateOfMaintenance             | GOED     
HISTORICAL | 438957 | 91157235 | stateOfMaintenanceRoof         | 0        
HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseContent | J        
HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseFire    | J        
ARCHIVED   | 451423 | 91157235 | isPropertyOwner                | TRUE     
ARCHIVED   | 451423 | 91157235 | livingAtProperty               | TRUE     
ARCHIVED   | 451423 | 91157235 | stateOfMaintenance             | GOED     
ARCHIVED   | 451423 | 91157235 | stateOfMaintenanceRoof         | 0        
ARCHIVED   | 451423 | 91157235 | unsecuredGuaranteeHouseContent | J        
ARCHIVED   | 451423 | 91157235 | unsecuredGuaranteeHouseFire    | J        


And here's a dbfiddle