[sql] How do I alter the position of a column in a PostgreSQL database table?

I've tried the following, but I was unsuccessful:

ALTER TABLE person ALTER COLUMN dob POSITION 37;

I use Django and it requires id column in each table if you don't want to have a headache. Unfortunately, I was careless and my table bp.geo_location_vague didn't contain this field. I initialed little trick. Step 1:

CREATE VIEW bp.geo_location_vague_vw AS
    SELECT 
        a.id, -- I change order of id column here. 
        a.in_date,
        etc
    FROM bp.geo_location_vague a

Step 2: (without create table - table will create automaticaly!)

SELECT * into bp.geo_location_vague_cp2 FROM bp.geo_location_vague_vw

Step 3:

CREATE SEQUENCE bp.tbl_tbl_id_seq;
ALTER TABLE bp.geo_location_vague_cp2 ALTER COLUMN id SET DEFAULT nextval('tbl_tbl_id_seq');
ALTER SEQUENCE bp.tbl_tbl_id_seq OWNED BY bp.geo_location_vague_cp2.id;
SELECT setval('tbl_tbl_id_seq', COALESCE(max(id), 0)) FROM bp.geo_location_vague_cp2;

Because I need have bigserial pseudotype in the table. After SELECT * into pg will create bigint type insetad bigserial.

step 4: Now we can drop the view, drop source table and rename the new table in the old name. The trick was ended successfully.

Open the table in PGAdmin and in the SQL pane at the bottom copy the SQL Create Table statement. Then open the Query Tool and paste. If the table has data, change the table name to 'new_name', if not, delete the comment "--" in the Drop Table line. Edit the column sequence as required. Mind the missing/superfluous comma in the last column in case you have moved it. Execute the new SQL Create Table command. Refresh and ... voilà.

For empty tables in the design stage this method is quite practical.

In case the table has data, we need to rearrange the column sequence of the data as well. This is easy: use INSERT to import the old table into its new version with:

INSERT INTO new ( c2, c3, c1 ) SELECT * from old;

... where c2, c3, c1 are the columns c1, c2, c3 of the old table in their new positions. Please note that in this case you must use a 'new' name for the edited 'old' table, or you will lose your data. In case the column names are many, long and/or complex use the same method as above to copy the new table structure into a text editor, and create the new column list there before copying it into the INSERT statement.

After checking that all is well, DROP the old table and change the the 'new' name to 'old' using ALTER TABLE new RENAME TO old; and you are done.

One, albeit a clumsy option to rearrange the columns when the column order must absolutely be changed, and foreign keys are in use, is to first dump the entire database with data, then dump just the schema (pg_dump -s databasename > databasename_schema.sql). Next edit the schema file to rearrange the columns as you would like, then recreate the database from the schema, and finally restore the data into the newly created database.

I don't think you can at present: see this article on the Postgresql wiki.

The three workarounds from this article are:

1. Recreate the table
2. Add columns and move data
3. Hide the differences with a view.

I was working on re-ordering a lot of tables and didn't want to have to write the same queries over and over so I made a script to do it all for me. Essentially, it:

1. Gets the table creation SQL from pg_dump
2. Gets all available columns from the dump
3. Puts the columns in the desired order
4. Modifies the original pg_dump query to create a re-ordered table with data
5. Drops old table
6. Renames new table to match old table

It can be used by running the following simple command:

./reorder.py -n schema -d database table \
    first_col second_col ... penultimate_col ultimate_col --migrate

It prints out the sql so you can verify and test it, that was a big reason I based it on pg_dump. You can find the github repo here.

I don't think you can at present: see this article on the Postgresql wiki.

The three workarounds from this article are:

1. Recreate the table
2. Add columns and move data
3. Hide the differences with a view.

In PostgreSQL, while adding a field it would be added at the end of the table. If we need to insert into particular position then

alter table tablename rename to oldtable;
create table tablename (column defs go here);
insert into tablename (col1, col2, col3) select col1, col2, col3 from oldtable;

There are some workarounds to make it possible:

1. Recreating the whole table

2. Create new columns within the current table

3. Create a view

https://tableplus.com/blog/2018/09/postgresql-is-it-possible-to-alter-column-order-position-in-a-table.html

I don't think you can at present: see this article on the Postgresql wiki.

The three workarounds from this article are:

1. Recreate the table
2. Add columns and move data
3. Hide the differences with a view.

I was working on re-ordering a lot of tables and didn't want to have to write the same queries over and over so I made a script to do it all for me. Essentially, it:

1. Gets the table creation SQL from pg_dump
2. Gets all available columns from the dump
3. Puts the columns in the desired order
4. Modifies the original pg_dump query to create a re-ordered table with data
5. Drops old table
6. Renames new table to match old table

It can be used by running the following simple command:

./reorder.py -n schema -d database table \
    first_col second_col ... penultimate_col ultimate_col --migrate

It prints out the sql so you can verify and test it, that was a big reason I based it on pg_dump. You can find the github repo here.

One, albeit a clumsy option to rearrange the columns when the column order must absolutely be changed, and foreign keys are in use, is to first dump the entire database with data, then dump just the schema (pg_dump -s databasename > databasename_schema.sql). Next edit the schema file to rearrange the columns as you would like, then recreate the database from the schema, and finally restore the data into the newly created database.

In PostgreSQL, while adding a field it would be added at the end of the table. If we need to insert into particular position then

alter table tablename rename to oldtable;
create table tablename (column defs go here);
insert into tablename (col1, col2, col3) select col1, col2, col3 from oldtable;

This post is old and probably solved but I had the same issue. I resolved it by creating a view of the original table specifying the new column order.

From here I could either use the view or create a new table from the view.

    CREATE VIEW original_tab_vw AS
    SELECT a.col1, a.col3, a.col4, a.col2
    FROM original_tab a
    WHERE a.col1 IS NOT NULL --or whatever

    SELECT * INTO new_table FROM original_tab_vw

Rename or drop the original table and set the name of the new table to the old table.

I don't think you can at present: see this article on the Postgresql wiki.

The three workarounds from this article are:

1. Recreate the table
2. Add columns and move data
3. Hide the differences with a view.

Open the table in PGAdmin and in the SQL pane at the bottom copy the SQL Create Table statement. Then open the Query Tool and paste. If the table has data, change the table name to 'new_name', if not, delete the comment "--" in the Drop Table line. Edit the column sequence as required. Mind the missing/superfluous comma in the last column in case you have moved it. Execute the new SQL Create Table command. Refresh and ... voilà.

For empty tables in the design stage this method is quite practical.

In case the table has data, we need to rearrange the column sequence of the data as well. This is easy: use INSERT to import the old table into its new version with:

INSERT INTO new ( c2, c3, c1 ) SELECT * from old;

... where c2, c3, c1 are the columns c1, c2, c3 of the old table in their new positions. Please note that in this case you must use a 'new' name for the edited 'old' table, or you will lose your data. In case the column names are many, long and/or complex use the same method as above to copy the new table structure into a text editor, and create the new column list there before copying it into the INSERT statement.

After checking that all is well, DROP the old table and change the the 'new' name to 'old' using ALTER TABLE new RENAME TO old; and you are done.

This post is old and probably solved but I had the same issue. I resolved it by creating a view of the original table specifying the new column order.

From here I could either use the view or create a new table from the view.

    CREATE VIEW original_tab_vw AS
    SELECT a.col1, a.col3, a.col4, a.col2
    FROM original_tab a
    WHERE a.col1 IS NOT NULL --or whatever

    SELECT * INTO new_table FROM original_tab_vw

Rename or drop the original table and set the name of the new table to the old table.

I use Django and it requires id column in each table if you don't want to have a headache. Unfortunately, I was careless and my table bp.geo_location_vague didn't contain this field. I initialed little trick. Step 1:

CREATE VIEW bp.geo_location_vague_vw AS
    SELECT 
        a.id, -- I change order of id column here. 
        a.in_date,
        etc
    FROM bp.geo_location_vague a

Step 2: (without create table - table will create automaticaly!)

SELECT * into bp.geo_location_vague_cp2 FROM bp.geo_location_vague_vw

Step 3:

CREATE SEQUENCE bp.tbl_tbl_id_seq;
ALTER TABLE bp.geo_location_vague_cp2 ALTER COLUMN id SET DEFAULT nextval('tbl_tbl_id_seq');
ALTER SEQUENCE bp.tbl_tbl_id_seq OWNED BY bp.geo_location_vague_cp2.id;
SELECT setval('tbl_tbl_id_seq', COALESCE(max(id), 0)) FROM bp.geo_location_vague_cp2;

Because I need have bigserial pseudotype in the table. After SELECT * into pg will create bigint type insetad bigserial.

step 4: Now we can drop the view, drop source table and rename the new table in the old name. The trick was ended successfully.