I have 2 Postgres tables with the following structure:
Table "public.tmp"
Column | Type | Collation | Nullable | Default
-------------------+-------------------------+-----------+----------+---------
MY_SL | character varying(50) | | |
Release | character varying(50) | | |
HOST | character varying(50) | | not null |
UN NO. | character varying(50) | | |
STATUS | character varying(50) | | |
S_DATE | character varying(50) | | not null |
Table "public.mo"
Column | Type | Collation | Nullable | Default
-------------------+-------------------------+-----------+----------+-----------------------------------------
id | integer | | not null | nextval('mbss_output_id_seq'::regclass)
HOST | character varying(50) | | not null |
UN NO. | character varying(50) | | |
STATUS | character varying(50) | | |
S_DATE | character varying(50) | | not null |
compliant_status | character varying(50) | | not null |
Lets say I have data in the tmp table as below:
Table: tmp MY_SL | Release | HOST | UN NO.| STATUS | S_DATE ------------+-------------+-----------+----------------------+------------------ 2 | 1 | RhelTest | 7:1:8 | COMPLIANT | 2020-08-26T15:16:48Z 12 | 1 | RhelTest | 7:1:9 | COMPLIANT | 2020-08-26T15:16:48Z 22 | 2 | RhelTest | 7:2:1 | COMPLIANT | 2020-08-26T15:16:48Z 4 | 1 | RhelTest | 7:2:10 | NC | 2020-08-26T15:16:48Z 11 | 2 | RhelTest | 7:2:11 | NC | 2020-08-26T15:16:48Z 1 | 3 | Demo1 | 7:2:11 | NC | 2020-08-26T15:16:48Z 23 | 3 | Demo1 | 7:2:11 | NC | 2020-08-26T15:16:48Z 333 | 3 | Demo2 | 7:2:11 | COMPLIANT | 2020-08-26T15:16:48Z
Now I want to write a psql INSERT INTO query which will copy the data from public.tmp to public.mo table and also perform below condition
When a Host has mix values in STATUS column, for eg if HOST: RhelTest
- has 2 values in STATUS column as ‘COMPLIANT’ and ‘NC’ then the column compliant_status should have the value ‘PARTIAL’ for such rows
- or if there is only one value like ‘COMPLIANT’ then the column compliant_status should have the value ‘COMPLIANT’ for such rows
- or if there is only one value like ‘NC’ then the column compliant_status should have the value ‘NON_COMPLIANT’ for such rows
Finally Expected output in the public.mo table:
Table: public.mo id | HOST | UN NO.| STATUS | S_DATE | compliant_status ------------+-------------+-----------+----------------------+------------------ 1 | RhelTest | 7:1:8 | COMPLIANT | 2020-08-26T15:16:48Z | PARTIAL 2 | RhelTest | 7:1:9 | COMPLIANT | 2020-08-26T15:16:48Z | PARTIAL 3 | RhelTest | 7:2:1 | COMPLIANT | 2020-08-26T15:16:48Z | PARTIAL 4 | RhelTest | 7:2:10 | NC | 2020-08-26T15:16:48Z | PARTIAL 5 | RhelTest | 7:2:11 | NC | 2020-08-26T15:16:48Z | PARTIAL 6 | Demo1 | 7:2:11 | NC | 2020-08-26T15:16:48Z | NON_COMPLIANT 7 | Demo1 | 7:2:11 | NC | 2020-08-26T15:16:48Z | NON_COMPLIANT 8 | Demo2 | 7:2:11 | COMPLIANT | 2020-08-26T15:16:48Z | COMPLIANT
Advertisement
Answer
You can use window functions and a case expression. Here is one way to do it, assuming that there are only two possible values for status, as shown in your data:
insert in mo (host, un_no, status, s_date, compliant_status)
select host, un_no, status, s_date,
case
when min_host_status <> max_host_status then 'PARTIAL'
when min_host_status = 'NC' then 'NON_COMPLIANT'
else min_host_status
end
from (
select t.*,
min(status) over(partition by host) min_host_status,
max(status) over(partition by host) max_host_status
from tmp t
) t
The idea is to compare the minimum and maximum values of status for each host. If there are different, then compliant_status is “PARTIAL”. Else, we turn “NC” to “NON_COMPLIANT”, and leave the other value (“COMPLIANT”) as it is.
The subquery is not strictly necessary here, we could very well use the window functions directly in the outer query; I used it because it avoids repeating the same expressions again and again.