Consumer Code Uniqueness Migration

Owned by Rohit shende (Unlicensed)
Last updated: Jul 14, 2020
2 min read

Initially in Demands, consumer code has the pattern like <PropertyId>:<Assessment No>. Now it has been decided to change the consumer code to pattern like <PropertyId> (just for reference) and check how the product works.

So to change the value of consumer code in database table(egbs_demand_v1), the migration script has been written the following step.

  1. Create a backup of the table egbs_demand_v1.

  2. Add new constraint for table egbs_demand_v1 and remove the old constraint first.

  3. Split the data from column consumercode base on the character ‘:’ like 

 String first-part  : String second-part  then column contains String first-part  from old old pattern.

During these changes, the logic behind the other column has not been changed, those remained the same as they were earlier.The above steps is to split the old consumercode pattern and make a new one.


After split it has been found that there are  multiple demand records of the same consumer code whose status is active for some financial year. To correct this error, table data must be updated. So that for a particular financial year, out of the multiple records of the same consumer code, the demand record which is recently modified / created only that record have the status value set to ACTIVE and other records have status value set to INACTIVE  

Migration script:

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; drop index uk_egbs_demand_v1_consumercode_businessservice; ALTER TABLE egbs_demand_v1 ADD CONSTRAINT uk_egbs_demand_v1_consumercode_businessservice UNIQUE (id,consumercode,tenantid,taxperiodfrom,taxperiodto,businessservice); UPDATE egbs_demand_v1 SET status = 'INACTIVE' FROM (SELECT consumercode,taxperiodfrom,taxperiodto, MAX(lastmodifiedtime) AS MAXTIME FROM egbs_demand_v1 GROUP BY consumercode,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.consumercode = demand.consumercode; UPDATE egbs_demand_v1 SET consumercode = SPLIT_PART(consumercode,':',1); UPDATE egcl_receiptheader SET consumercode = SPLIT_PART(consumercode,':',1); UPDATE egbs_billdetail_v1 SET consumercode = SPLIT_PART(consumercode,':',1); UPDATE egcl_bill SET consumercode = SPLIT_PART(consumercode,':',1);