1

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.