Migrating NULL Values of a Column to Another Column-CodePudding

I have two column table for orders,

ID OPEN CLOSE
1  {}   {}
2  {}   NULL
3  {}   {}

OPEN/CLOSE columns are JSONB columns containing a receipt from a third party API they are not queried often only used later for analytics reasons. In order to distinguish between completed orders and still waiting orders I check the CLOSEcolumn value if NOT NULL order is completed. However due to changes in the third party API I'll have to keep some JSON value in CLOSE column. My plan is to create a third column enum type called STATUS and keep track of the status of orders using this enum. My question is how to migrate data from CLOSE into STATUS every NOT NULLshould get one enum rest another.

ID OPEN CLOSE  STATUS
1  {}   {}     FILLED
2  {}   NULL   WAITING
3  {}   {}     FILLED

CodePudding user response：

This fills STATUS with one of two possible values depending on CLOSE having a value or not:

update orders set STATUS = (case when CLOSE is null then 'WAITING' else 'FILLED' end);

(not tested)

CodePudding user response：

Another option is creating a DateTime column named ClosedDate. NULL values in the new column will continue to be treated the same way you currently treat NULL values in the Close column; anything tells you the exact time the order closed. This will use less storage, while giving you more information at the ready.

... except of course for the existing data, where you don't have this information available via the schema. For these records, you could either break into the existing JSON data to find the value you want, or you could use an pre-determined value to mean the record was closed before the data was tracked.

So you could do something like this migrate the existing data:

UPDATE Orders 
SET ClosedDate = CASE WHEN Close IS NULL THEN NULL ELSE CURRENT_DATE END

If you do proceed with a status field (and there is a good argument for this vs my suggestion, because it sets you up for more different order states in the future), it might be better to use shorter status codes; there's no need to store the entire word in the table. This will fit a few more records per page, and help any indexing use less memory.