Обсуждение: Reordering the fields in a table

If there is a way to do this please let me know.

 

PostgreSQL Request

I would love to see an enhancement to PostgreSQL where administrators had some way to reorder the fields in their tables.  As a software developer I can easily write a tool to reorder the fields in a table if there were no foreign keys.  But foreign keys throw an enormous hurdle in my way.

 

Why is this a big deal?

Sixteen years ago when I started working at the University of Illinois I told my boss, “if you give me thirty days I can build a platform that will allow me to cut at least 50% off of the development time of your standard internal web business app.”  I built it.  Today I personally created/manage/develop 17 services that provide over 120 million pages of web content a year.  I can jump into this infrastructure and make a change in an hour or two that would take most people weeks to change.  The one issue that causes me the most grief is the readability of tables.  When you manage 17 services where you are continually tweaking the system you find the tables are very unreadable.  New fields are continually added to the bottom of tables. They are just not readable.

 

The most underappreciated elements in software design is the ongoing need to be able to easily read what you are working with.  Table can really become a mess.  It would be the most amazing enhancement to be able to order the fields of any table with a command.  I know today this is probably not a top priority to most developers and DBAs.  They work in an environment where one person might work on just one or two services.  But as development strategies change over time more people will be developing software faster and faster.  Table field readability will become more important.

 

Thanks,

 

LANCE CAMPBELL

Software Architect

 

Web Services

Public Affairs

Contact the Webtools Team

217.333.0382

lance@illinois.edu

 

 

 

Under the Illinois Freedom of Information Act any written communication to or from university employees regarding university business is a public record and may be subject to public disclosure.

 

There is no way. You should drop and re-create table with your order.

Regards.

Cum, 2021-04-16 tarihinde 16:15 +0000 saatinde, Campbell, Lance yazdı:

If there is a way to do this please let me know.

 

PostgreSQL Request

I would love to see an enhancement to PostgreSQL where administrators had some way to reorder the fields in their tables.  As a software developer I can easily write a tool to reorder the fields in a table if there were no foreign keys.  But foreign keys throw an enormous hurdle in my way.

 

Why is this a big deal?

Sixteen years ago when I started working at the University of Illinois I told my boss, “if you give me thirty days I can build a platform that will allow me to cut at least 50% off of the development time of your standard internal web business app.”  I built it.  Today I personally created/manage/develop 17 services that provide over 120 million pages of web content a year.  I can jump into this infrastructure and make a change in an hour or two that would take most people weeks to change.  The one issue that causes me the most grief is the readability of tables.  When you manage 17 services where you are continually tweaking the system you find the tables are very unreadable.  New fields are continually added to the bottom of tables. They are just not readable.

 

The most underappreciated elements in software design is the ongoing need to be able to easily read what you are working with.  Table can really become a mess.  It would be the most amazing enhancement to be able to order the fields of any table with a command.  I know today this is probably not a top priority to most developers and DBAs.  They work in an environment where one person might work on just one or two services.  But as development strategies change over time more people will be developing software faster and faster.  Table field readability will become more important.

 

Thanks,

 

LANCE CAMPBELL

Software Architect

 

Web Services

Public Affairs

Contact the Webtools Team

217.333.0382

lance@illinois.edu

 

 

 

Under the Illinois Freedom of Information Act any written communication to or from university employees regarding university business is a public record and may be subject to public disclosure.

 

On Fri, 2021-04-16 at 16:15 +0000, Campbell, Lance wrote:
> I would love to see an enhancement to PostgreSQL where administrators had some way to reorder the fields in their
tables.
>  
> Why is this a big deal?
> The one issue that causes me the most grief is the readability of tables.  When you manage 17 services
>  where you are continually tweaking the system you find the tables are very unreadable.
> New fields are continually added to the bottom of tables. They are just not readable.

There is a misconception here.
Database tables are not designed for human consumption.
If you want to present your table data in a readable format, you need a front end application.
Arranging table columns is the job of such a tool.

If you want to do it in the database, use a view.
Simple views that just reorder the columns are updatable, so you could just
use those instead of the underlying tables in your statements.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Right on, Laurenz!

Laurenz Albe wrote on 4/16/2021 3:54 PM:
> On Fri, 2021-04-16 at 16:15 +0000, Campbell, Lance wrote:
>> I would love to see an enhancement to PostgreSQL where administrators had some way to reorder the fields in their
tables.
>>   
>> Why is this a big deal?
>> The one issue that causes me the most grief is the readability of tables.  When you manage 17 services
>>   where you are continually tweaking the system you find the tables are very unreadable.
>> New fields are continually added to the bottom of tables. They are just not readable.
> There is a misconception here.
> Database tables are not designed for human consumption.
> If you want to present your table data in a readable format, you need a front end application.
> Arranging table columns is the job of such a tool.
>
> If you want to do it in the database, use a view.
> Simple views that just reorder the columns are updatable, so you could just
> use those instead of the underlying tables in your statements.
>
> Yours,
> Laurenz Albe

On 4/16/21 2:54 PM, Laurenz Albe wrote:
> On Fri, 2021-04-16 at 16:15 +0000, Campbell, Lance wrote:
>> I would love to see an enhancement to PostgreSQL where administrators had some way to reorder the fields in their
tables.
>>   
>> Why is this a big deal?
>> The one issue that causes me the most grief is the readability of tables.  When you manage 17 services
>>   where you are continually tweaking the system you find the tables are very unreadable.
>> New fields are continually added to the bottom of tables. They are just not readable.
> There is a misconception here.
> Database tables are not designed for human consumption.

Rubbish.  Database tables are designed by humans to store data that humans 
care about.

> If you want to present your table data in a readable format, you need a front end application.
> Arranging table columns is the job of such a tool.

Adding "ALTER TABLE MOVE COLUMN ... BEFORE/AFTER COLUMN ..." is pretty 
trivial, requiring "just another table", which stores the physical order of 
the columns, and also the order in which they are presented to the "user 
layer".  I know this because such a feature was implemented 15+ years ago in 
Rdb/VMS.

-- 
Angular momentum makes the world go 'round.

Wells Oliver schrieb am 17.04.2021 um 00:28:
> If it helps the cause, I would love this too, in addition to having
> the ability to ALTER TABLE ... ADD COLUMN with the ability to specify
> where in the column order. This ends up being a bigger frustration
> for a lot of people than it should be, and maybe it's a touch of OCD,
> but most RDBMS packages allow this

"Most" seems a bit of a stretch.
To my knowledge it's only supported in MySQL, Firebird and Informix.

You can't do this in Oracle, SQL Server, DB2, Teradata, Ingres, Vertica or SAP Hana.

MS SQL Server allows this too.  I do it all the time.

-----Original Message-----
From: Thomas Kellerer <shammat@gmx.net>
Sent: Saturday, April 17, 2021 1:09 AM
To: pgsql-admin@lists.postgresql.org
Subject: Re: Reordering the fields in a table

Wells Oliver schrieb am 17.04.2021 um 00:28:
> If it helps the cause, I would love this too, in addition to having
> the ability to ALTER TABLE ... ADD COLUMN with the ability to specify
> where in the column order. This ends up being a bigger frustration for
> a lot of people than it should be, and maybe it's a touch of OCD, but
> most RDBMS packages allow this

"Most" seems a bit of a stretch.
To my knowledge it's only supported in MySQL, Firebird and Informix.

You can't do this in Oracle, SQL Server, DB2, Teradata, Ingres, Vertica or SAP Hana.

If you want to present your table data in a readable format, you need a front end application.
Arranging table columns is the job of such a tool.

Adding "ALTER TABLE MOVE COLUMN ... BEFORE/AFTER COLUMN ..." is pretty trivial, requiring "just another table", which stores the physical order of the columns, and also the order in which they are presented to the "user layer".  I know this because such a feature was implemented 15+ years ago in Rdb/VMS.

mike.moening@rtssigns.com schrieb am 17.04.2021 um 16:32:
> MS SQL Server allows this too.  I do it all the time.

SSMS will do that for you by dropping and re-creating the table (and all related foreign keys)

But there is no SQL statement to do it.

See e.g. the manual:


https://docs.microsoft.com/en-us/sql/relational-databases/tables/change-column-order-in-a-table?view=sql-server-ver15#TsqlProcedure

>  To change the column order
>  This task is not supported using Transact-SQL statements.


> Wells Oliver schrieb am 17.04.2021 um 00:28:
>> If it helps the cause, I would love this too, in addition to having
>> the ability to ALTER TABLE ... ADD COLUMN with the ability to specify
>> where in the column order. This ends up being a bigger frustration for
>> a lot of people than it should be, and maybe it's a touch of OCD, but
>> most RDBMS packages allow this
>
> "Most" seems a bit of a stretch.
> To my knowledge it's only supported in MySQL, Firebird and Informix.
>
> You can't do this in Oracle, SQL Server, DB2, Teradata, Ingres, Vertica or SAP Hana.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> The feature has been discussed before on these lists and the only thing
> preventing its inclusion is some developers either donating their time to
> see it finished or some interested non-developers paying some developers to
> write it on their behalf.

It's not just whether somebody finishes a patch.  The real concern
here is that separating logical from physical column order would
inevitably cause great quantities of bugs, probably including some
on the client side.  If our experience with dropped columns is any
guide, it'd probably take *years* to flush out all the places that
misbehave when dealing with re-ordered columns.  So maybe it'll
happen eventually, but it'll be far from pain-free.

(FWIW, I recall that Alvaro Herrera had a semi-finished patch
in this area a few years ago.  If anyone is interested in
pursuing this, starting from that would likely be useful.)

            regards, tom lane

On 2021-Apr-16, Campbell, Lance wrote:

> If there is a way to do this please let me know.
> 
> PostgreSQL Request
> I would love to see an enhancement to PostgreSQL where administrators
> had some way to reorder the fields in their tables.  As a software
> developer I can easily write a tool to reorder the fields in a table
> if there were no foreign keys.  But foreign keys throw an enormous
> hurdle in my way.

Yeah, this feature has been requested many times in the past.  It's a
tough nut to crack though, which is why it doesn't exist today.  I had a
patch partially working that did it, but there were a number of areas in
the code I couldn't figure out how to change, and after spending much
more time than I should have, I abandoned it.

I have learned a few more things since then, and maybe if I attempted it
now, I could make it work.  But I have no plans to work on that at
present.

It is not impossible, just pretty difficult.

-- 
Álvaro Herrera                            39°49'30"S 73°17'W
Officer Krupke, what are we to do?
Gee, officer Krupke, Krup you! (West Side Story, "Gee, Officer Krupke")

On Fri, 2021-04-16 at 22:33 -0500, Ron wrote:
> On 4/16/21 2:54 PM, Laurenz Albe wrote:
> > On Fri, 2021-04-16 at 16:15 +0000, Campbell, Lance wrote:
> > > I would love to see an enhancement to PostgreSQL where administrators had some way to reorder the fields in their
tables.
> > 
> > There is a misconception here.
> > Database tables are not designed for human consumption.
> 
> Rubbish.  Database tables are designed by humans to store data that humans 
> care about.

I think you deliberately misunderstand me.
A database is not a GUI to render data.

> > If you want to present your table data in a readable format, you need a front end application.
> > Arranging table columns is the job of such a tool.
> 
> Adding "ALTER TABLE MOVE COLUMN ... BEFORE/AFTER COLUMN ..." is pretty 
> trivial, requiring "just another table", which stores the physical order of 
> the columns, and also the order in which they are presented to the "user 
> layer".  I know this because such a feature was implemented 15+ years ago in 
> Rdb/VMS.

It is *not* trivial.

If you want a solution and are not just trolling, why don't you use views?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com