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 | COMPLIANTAdvertisement
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 endfrom ( select t.*, min(status) over(partition by host) min_host_status, max(status) over(partition by host) max_host_status from tmp t) tThe 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.