Reorder postgres tables moving long columns to end of table for better debugging

[corneliusroemer]

I'm currently debugging a confusing state of loculus and looking at database tables.

Something that would make debugging more pleasant is if long columns were at the right hand side of columns, so one can see all short columns on one screen.

This is the current situation:

It would be nice if I didn't have to scroll right for ages to see the rest:

This might be as easy as reordering the schema?

I know I can do the following but still, would be nice if one could select * from sequence_entries; etc

select s.accession, s.version, s.pipeline_version, s.errors, s.warnings, s.processing_status, s.started_processing_at, s.finished_processing_at   from sequence_entries_preprocessed_data as s LIMIT 2;

[chaoran-chen]

As we decided to start version and migrate the database from now on, we won't be able to make re-ordering of the columns anymore.

[corneliusroemer]

I'm not sure I understand, wouldn't migrations exactly allow us to do that? Changing the schema, e.g. reordering columns?

[chaoran-chen]

The column order is a database-internal thing and nothing that (standard) SQL really allows you to control: https://stackoverflow.com/questions/285733/how-do-i-alter-the-position-of-a-column-in-a-postgresql-database-table

[corneliusroemer]

Just coming across this again when trying to debug, it is harmful that the json column is in the middle. We should really see whether we can move it to the end somehow. This could involve a view for example, we don't have to alter the actual table necessarily.

One other idea occurs to me: you can define a VIEW that specifies the order of columns how you like it, without changing the physical position of the column in the base table.

[chaoran-chen]

Why don't you just write a corresponding query when you look at the data? The returned results are in the order of their mentions in the select clause.

[corneliusroemer]

Too tedious to do every time.

I don't want to hand write this every time:

SELECT  group_id, submitted_at, released_at, is_revocation, version, organism, submission_id, submitter, approver, accession FROM sequence_entries;

View is the perfect solution I guess, can prefix it like _debug or something

[chaoran-chen]

I don't think that it is a good idea to add views for purely debugging purposes for a production system. What about having a few SQL scripts that you can call? We can add a folder somewhere with a collection of useful scripts.

[chaoran-chen]

I also recommend using a GUI to look up database tables. In DataGrip, it is easy to change the order of columns simply by drag-and-dropping the column headers. (I just tested: DataGrip even remembers the order if you change it.)

[chaoran-chen]

I'll close this again as the original suggestion to "Reorder postgres tables" is not really possible since we started database schema versioning (at least not without recreating and copying all data).