Thread: Please add the ability to add columns into the middle of a table.

Having the ability to insert a column in the middle of a table using the PGAdmin 3 UI would be very nice. Benefits
includegrouping related fields and creating a column list in the natural flow of data entry. Having it automated within
theUI would make this piece of DB management so much easier.<br /><br /> I am able to write scripts that do this but it
takesa long time to write the scripts on tables with lots of dependencies, partly because:<br /> 1) Dependencies and
constraintshave to be dropped first and in the correct order.<br /> 2) Dependencies and constraints have to be
recreatedin the correct order.<br /><br /> Basically, the scripts I write perform these steps:<br /> 1) Drop
constraints.Sometimes I'll also drop a sequence, but not always.<br /> 2) Drop dependencies in the correct order. The
challengeis getting the order correct. I have to run the script within a transaction and see what errors come up. Roll
backany changes, fix any errors and try again.<br /> 3) Rename the original table.<br /> 4) Create a replacement table
withthe new columns in the position I choose. I do this by copying the existing table definition first. This makes it
easyto create the new table with constraints, indexes, permissions and comments.<br /> 5) Make sure any sequence used
bythe table has the correct value.<br /> 6) Drop the original table.<br /> 7) Rebuild dependencies (in the correct
order).<br/><br /><div class="moz-signature">-- <br /><b><i>Patrick Headley</i></b><br /> Linx Consulting, Inc.<br /><a
class="moz-txt-link-abbreviated"href="mailto:pheadley@linxco-inc.com">pheadley@linxco-inc.com</a><br /><a
class="moz-txt-link-abbreviated"href="http://www.linxco-inc.com">www.linxco-inc.com</a></div> 

I would like it if pgAdmin could generate the script to insert table columns into the middle of an existing table. This is something that is available in the SQL Server Management Studio, which uses essentially the same steps that I described in my initial request. So, hte answer is neither A or B but to script the insertion of columns into an existing table, which requires the creation of a replacement table with the new columns.

I've attached one of my scripts to this message. This script is pretty complex.

On Fri, May 6, 2016 at 12:18 PM, Patrick Headley <pheadley@linxco-inc.com> wrote:

Having the ability to insert a column in the middle of a table using the PGAdmin 3 UI would be very nice. Benefits include grouping related fields and creating a column list in the natural flow of data entry. Having it automated within the UI would make this piece of DB management so much easier.

What are you asking for here?

A) For pgAdmin to run a complex algorithm to physcially re-order columns by creating a new table?

​ B) For PostgreSQL itself to allow for column re-ordering?

The later has been discussed many times, including relatively recently, but hasn't made it into -core (not even sure if patches have been proposed.

​The data entry aspect can be handled quite readily without any interaction with the physical table.  Having pgAdmin or some other application maintain an ordering table that defaults to physical but can be overridden would be a somewhat trivial project compared to what you propose.

David J.​

I am certainly open to the wiki idea. Each script has to be customized so only a description of the steps and samples could be included. How do I go about adding this to the wiki? Is there someone else that maintains the wiki that I can contact? Is it something I could write up and turn over to the wiki maintainer for publishing?

The idea for the script comes from SQL Server Management Studio, Microsoft Access and, while I haven't used MySQL in quite a while, I believe it can also handle the insertions of fields in the middle of a table using the SQL Workbench product. I use it on an almost daily basis in the work I do. I imagine the benefits for users, once working would outweigh the complaints that you would receive during development. For me, the benefits would be that I could apply the time it takes to write a script towards modifying my clients' products in other ways. Please reconsider your original reply.

And thank you for the quick response to my initial request.

On Fri, May 6, 2016 at 12:53 PM, Patrick Headley <pheadley@linxco-inc.com> wrote:

I would like it if pgAdmin could generate the script to insert table columns into the middle of an existing table. This is something that is available in the SQL Server Management Studio, which uses essentially the same steps that I described in my initial request. So, hte answer is neither A or B but to script the insertion of columns into an existing table, which requires the creation of a replacement table with the new columns.

I've attached one of my scripts to this message. This script is pretty complex.

​

That qualifies as option A

If it can be done via a script I'd suggest just adding said script to our Wiki.  I see very little benefit to having this embedded into pgAdmin.​

But hey, maybe this is something the pgAdmin4 team would want to consider.

Without native column-reorder support in the database I'm highly skeptical of adding something like this into pgAdmin.  The complaints we're likely to receive by users outweigh the benefit, IMO.

David J.

On 2016-05-06 12:24 PM, David G. Johnston wrote:
> ​B) For PostgreSQL itself to allow for column re-ordering?
>
> The later has been discussed many times, including relatively recently, but
> hasn't made it into -core (not even sure if patches have been proposed.

I hope that feature makes it into core.  Its long overdue.  Now that UPSERT is 
done with 9.5, this is one of the outstanding features of MySQL that is actually 
useful and Postgres lacks.

One place I found such a feature useful a few years back is when I wanted to 
make a generic auditing trigger reusable for any table where the corresponding 
audit table had a few specified extra columns.

create function save_del_to_history() returns trigger language plpgsql as $$
begin    execute        format( 'insert into %I.%I select $1, $2.*', tg_table_schema, 
concat(tg_table_name,'_history') )        using currval('changeset_id_gen'::regclass), old;    return null;
end
$$;

Using that single procedure I could just do the following for each table "foo":

create trigger save_del_to_history    after update or delete on foo    for each row execute procedure
save_del_to_history();

However this approach is fragile due to the lack of column re-ordering because 
the extra auditing columns have to be the first columns of the audit tables in 
order for this to not break if regular table columns are added to a foo; if the 
audit fields were last they would appear in the middle of the regular field 
list, so the * wouldn't work right; and that in turn constrains being able to 
add or change auditing fields.

The above example was written against Postgres 9.1, I don't know if other 
relevant things changed on newer versions to improve such reusability.

I grant that normally field order shouldn't matter, and in the relational model 
fields aren't ordered, but in SQL they are significant.

-- Darren Duncan