How do I alter the position of a column in a PostgreSQL database table

Question

I ve tried the following  but I was unsuccessful   ALTER TABLE person ALTER COLUMN dob POSITION 37

User · Answer

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.

User · Answer

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    Gets the table creation SQL from pg dump Gets all available columns from the dump Puts the columns in the desired order Modifies the original pg dump query to create a re-ordered table with data Drops old table 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

User · Answer

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    Gets the table creation SQL from pg dump Gets all available columns from the dump Puts the columns in the desired order Modifies the original pg dump query to create a re-ordered table with data Drops old table 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

User · Answer

I don t think you can at present  see this article on the Postgresql wiki   The three workarounds from this article are     Recreate the table Add columns and move data Hide the differences with a view

User · Answer

Alter column position  in the PostgreSQL Wiki says      PostgreSQL currently defines column   order based on the attnum column of   the pg attribute table  The only way   to change column order is either by   recreating the table  or by adding   columns and rotating data until you   reach the desired layout    That s pretty weak  but in their defense  in standard SQL  there is no solution for repositioning a column either   Database brands that support changing the ordinal position of a column are defining an extension to SQL syntax   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

User · Answer

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

User · Answer

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  gt  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

User · Answer

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

User · Answer

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

User · Answer

There are some workarounds to make it possible    Recreating the whole table Create new columns within the current table Create a view   https   tableplus com blog 2018 09 postgresql-is-it-possible-to-alter-column-order-position-in-a-table html

User · Answer

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  gt  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

User · Answer

I don t think you can at present  see this article on the Postgresql wiki   The three workarounds from this article are     Recreate the table Add columns and move data Hide the differences with a view

User · Answer

There are some workarounds to make it possible    Recreating the whole table Create new columns within the current table Create a view   https   tableplus com blog 2018 09 postgresql-is-it-possible-to-alter-column-order-position-in-a-table html

User · Answer

I don t think you can at present  see this article on the Postgresql wiki   The three workarounds from this article are     Recreate the table Add columns and move data Hide the differences with a view

User · Answer

Alter column position  in the PostgreSQL Wiki says      PostgreSQL currently defines column   order based on the attnum column of   the pg attribute table  The only way   to change column order is either by   recreating the table  or by adding   columns and rotating data until you   reach the desired layout    That s pretty weak  but in their defense  in standard SQL  there is no solution for repositioning a column either   Database brands that support changing the ordinal position of a column are defining an extension to SQL syntax   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

User · Answer

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

User · Answer

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.

User · Answer

Alter column position  in the PostgreSQL Wiki says      PostgreSQL currently defines column   order based on the attnum column of   the pg attribute table  The only way   to change column order is either by   recreating the table  or by adding   columns and rotating data until you   reach the desired layout    That s pretty weak  but in their defense  in standard SQL  there is no solution for repositioning a column either   Database brands that support changing the ordinal position of a column are defining an extension to SQL syntax   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

User · Answer

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

User · Answer

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

User · Answer

Alter column position  in the PostgreSQL Wiki says      PostgreSQL currently defines column   order based on the attnum column of   the pg attribute table  The only way   to change column order is either by   recreating the table  or by adding   columns and rotating data until you   reach the desired layout    That s pretty weak  but in their defense  in standard SQL  there is no solution for repositioning a column either   Database brands that support changing the ordinal position of a column are defining an extension to SQL syntax   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

User · Answer

I don t think you can at present  see this article on the Postgresql wiki   The three workarounds from this article are     Recreate the table Add columns and move data Hide the differences with a view

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

Examples related to sql

Examples related to database

Examples related to postgresql

Examples related to alter-table