How to best handle the situation where my "table creation code" and my actual live tables have become desynced? I'm developing a system which, relevant to this question, consists of PostgreSQL database tables.
When I first created the SQL code to CREATE TABLE, I did think it through. However, over the years, I've thought of improvements, fixed bugs, and implemented new functionality. Each time I do this, on the &quot;live&quot; table containing my data, I then make sure to make the appropriate change to the code file, so that (at least in theory) I could run it on a different computer or fresh PostgreSQL installation and get an identical &quot;structure&quot;.
However, as we all are painfully aware of, it's impossible to change the column order. So already that is a change between my live table and the code. There's also the possibility that I simply forget to change it after fiddling with some problem for hours.
Either way, it's not a good feeling that my table creation code and my live tables are &quot;slightly different&quot;, if only in the column order and some of the identifiers for constraints which I never refer to in my code, and thus &quot;don't matter&quot;. But it's not a nice feeling.
I can't just nuke the tables and re-run my code to recreate the structure, because then I lose all the data in them!
Is there any reasonable way for me to &quot;sync&quot; my code and the live table structure while retaining the data, in a safe and problem-free manner?
There are times where someone wants to write code and not specify each individual column. If I write a script to split out one customers data to another database, not specifying the column names means that I don't need to change the script every time I add a new column. As long as the column order is constant. If there is one table that you want to change the column order, you can drop the constraints and indexes, rename the table, create the table again without the indexes and constraints, insert the data by specifying column names and create the indexes and constraints. It is a lot of work and requires downtime. But it would allow you to change the column order. Once you do that, you should update the tables using a tool like Flyway or ant where you can run code against each database as a release to make sure that the changes happen in the same way.