...
Create a backup of the table egbs_demand_v1.
Add new constraint for table egbs_demand_v1 and remove the old constraint first.
Split the data from column consumercode base on the character ‘:’ like
...
| Code Block |
|---|
CREATE TABLE demand_v1_copy
(
id character varying(64) NOT NULL,
consumercode character varying(250) NOT NULL,
consumertype character varying(250) NOT NULL,
businessservice character varying(250) NOT NULL,
payer character varying(250),
taxperiodfrom bigint NOT NULL,
taxperiodto bigint NOT NULL,
createdby character varying(256) NOT NULL,
createdtime bigint NOT NULL,
lastmodifiedby character varying(256),
lastmodifiedtime bigint,
tenantid character varying(250) NOT NULL,
minimumamountpayable numeric(12,2),
status character varying(64),
additionaldetails json,
billexpirytime bigint
);
INSERT INTO demand_v1_copy SELECT * FROM egbs_demand_v1;
ALTERdrop TABLEindex uk_egbs_demand_v1_consumercode_businessservice;
ADD
codeid VARCHAR(250) NULL;
UPDATE ALTER TABLE egbs_demand_v1 ADD CONSTRAINT uk_egbs_demand_v1_consumercode_businessservice SET codeid = SPLIT_PART(consumercode,':',1UNIQUE (id,consumercode,tenantid,taxperiodfrom,taxperiodto,businessservice);
UPDATE egbs_demand_v1 SET status = 'INACTIVE' FROM (SELECT codeidconsumercode,taxperiodfrom,taxperiodto, MAX(lastmodifiedtime) AS MAXTIME FROM egbs_demand_v1 GROUP BY codeidconsumercode,taxperiodfrom,taxperiodto) AS demand WHERE egbs_demand_v1.taxperiodfrom = demand.taxperiodfrom AND egbs_demand_v1.taxperiodto = demand.taxperiodto AND egbs_demand_v1.lastmodifiedtime < demand.MAXTIME AND egbs_demand_v1.codeidconsumercode = demand.codeidconsumercode;
UPDATE egbs_demand_v1 SET consumercode = codeidSPLIT_PART(consumercode,':',1);
ALTERUPDATE TABLE egbs_demand_v1 DROP COLUMN codeidegcl_receiptheader SET consumercode = SPLIT_PART(consumercode,':',1);
UPDATE egclegbs_billdetail_receiptheaderv1 SET consumercode = SPLIT_PART(consumercode,':',1);
UPDATE egbsegcl_billdetail_v1bill SET consumercode = SPLIT_PART(consumercode,':',1);
|