Reordering Columns in PostgreSQL

2 pointsHNBRN3y ago8 comments

This is kind of a stupid question but I see lot of people here recommend postgresql so I decided to give it a try.

I stumbled upon the fact that it's not possible to rearrange columns ordering.

Is this not too much of a constraint ? I mean what's the workaround for eg for the analysts using it daily ? Columns just pile at the end of every table without any consistency ?

Sorry for the broken english (french)

8 comments

rangerelf3y ago

The order of the columns in your table creation statements is irrelevant to the order of how the columns return to you in your queries.

Your select statement specified the order of your result columns in your result set. Your visualization tools should have the option to reorder the displayed columns to your liking or need.

sargstuff3y ago

visual example : https://www.w3resource.com/sql-exercises/sql-retrieve-exerci...

verbal examples : discussion on various ways to reorder columns : https://stackoverflow.com/questions/1605144/how-to-change-co...

HNBRNOP3y ago

Thanks. I checked and dbeaver can indeed store columns ordering as a filtered view. Seems like a bit overcomplicated to me but I guess I've been spoiled with mysql in which the ordering is trivial.

sargstuff3y ago

Alternate interpretation of explaination:

https://www.quackit.com/sql/tutorial/sql_order_by.cfm

https://www.tutorialsteacher.com/sqlserver/orderby

wcorrales3y ago

https://github.com/TriangleCommunications/reorder-table-colu...

sargstuff3y ago

Reviewing database normal forms might be helpful/useful. [1]

Non-postgress, old school method:

1) Dump table data to ascii delimited file.

2) Use awk/gawk to re-arrange columns in ascii delimited file.

3) drop old data table.

4) create new data table to upload the awk/gawk data generated in #2.

5) upload awk/gawk data from #2.

--------

google translate might be helpful : https://translate.google.com/

[1] : https://www.geeksforgeeks.org/first-normal-form-1nf/?ref=lbp

few other related links, as some things get lost when translating between languages.

https://www.linkedin.com/learning/programming-foundations-da...

https://www.cs.dartmouth.edu/~cs61/Resources/Papers/CACM%20K...

https://www.i-programmer.info/babbages-bag/292-codd-and-his-...

HNBRNOP3y ago

Thanks, we could definitely start from this

sargstuff3y ago

resources:

awk: the power and promis of a 40 yr old language (2021) : https://www.fosslife.org/awk-power-and-promise-40-year-old-l...

Gawk: Effective AWK programming https://www.gnu.org/software/gawk/manual/

gawk-psql : pgsql extension of GNU awk : https://gawkextlib.sourceforge.net/pgsql/pgsql.html

gawkextlib : http://shouce.jb51.net/gawk/gawkextlib.html

gawk libraries for XML, PostgreSQL : https://osdn.net/projects/sfnet_gawkextlib/

psql : https://www.tutorialspoint.com/unix_commands/psql.htm

postgresql copy from program : https://stackoverflow.com/questions/62254888/using-awk-and-p...

user-defined modules to postgres : https://www.highgo.ca/2019/10/01/a-guide-to-create-user-defi...

j / k navigate · click thread line to collapse

8 comments

rangerelf3y ago

The order of the columns in your table creation statements is irrelevant to the order of how the columns return to you in your queries.

Your select statement specified the order of your result columns in your result set. Your visualization tools should have the option to reorder the displayed columns to your liking or need.

sargstuff3y ago

visual example : https://www.w3resource.com/sql-exercises/sql-retrieve-exerci...

verbal examples : discussion on various ways to reorder columns : https://stackoverflow.com/questions/1605144/how-to-change-co...

HNBRNOP3y ago

Thanks. I checked and dbeaver can indeed store columns ordering as a filtered view. Seems like a bit overcomplicated to me but I guess I've been spoiled with mysql in which the ordering is trivial.

sargstuff3y ago

Alternate interpretation of explaination:

https://www.quackit.com/sql/tutorial/sql_order_by.cfm

https://www.tutorialsteacher.com/sqlserver/orderby

wcorrales3y ago

https://github.com/TriangleCommunications/reorder-table-colu...

sargstuff3y ago

Reviewing database normal forms might be helpful/useful. [1]

Non-postgress, old school method:

1) Dump table data to ascii delimited file.

2) Use awk/gawk to re-arrange columns in ascii delimited file.

3) drop old data table.