QUESTION: How to handle Schema changes properly?

[jfbaro]

Hello there!

Imagine I have a temporal table for personal information:

• UUID (varchar)
• main_document (varchar)
• name (varchar)
• DoB (timestamp)
• genre (varchar)
• address (varchar)
• salary (decimal)

at T1 I run a schema migration and add a new column, from now on the table has:

• UUID (varchar)
• main_document (varchar)
• name (varchar)
• DoB (timestamp)
• genre (varchar)
• address (varchar)
• salary (decimal)
• EMAIL (varchar)*

Then at T2 I run another schema migration and change the data type of main_document to NUMBER.

• UUID (varchar)
• main_document (NUMBER)*
• name (varchar)
• DoB (timestamp)
• genre (varchar)
• address (varchar)
• salary (decimal)
• email (varchar)

Then at T3 I run another schema migration and remove the genre column

• UUID (varchar)
• main_document (number)
• name (varchar)
• DoB (timestamp)
• ---------------*
• address (varchar)
• salary (decimal)
• email (varchar)

Then at T4 I run another schema migration and add the genre column, but now it has the data type NUMBER.

• UUID (varchar)
• main_document (number)
• name (varchar)
• DoB (timestamp)
• genre (NUMBER)*
• address (varchar)
• salary (decimal)
• email (varchar)

How can I query my DB (going back in time) without breaking my existing queries? SQL:2016

Are there any best practices or strategies to avoid all this complexity with these temporal tables + schema migration?

Any help would be much appreciated.

P.S: I didn't find the RIGHT place to post a question to the community. If that's the wrong place, please feel free to remove it. (sorry)

Thanks

[goccert25]

Hey @jfbaro, I know this is kind of old, but did you ever find the answer to your question? I too have the exact same one

[hettie-d]

Hi @goccert25 - this repo is not supported since Dec 2022, could you please re-post it at https://github.com/hettie-d/pg_bitemporal?

(I do not have good answer, but at least that's a place for discussions!)