How to change the position of a column in a PostgreSQL database table?

Question

I tried the following, but I was unsuccessful:

ALTER TABLE person ALTER COLUMN dob POSITION 37; 

Answer 1

The " Change Column Position " in the PostgreSQL Wiki says:

PostgreSQL currently defines a column order based on the attnum column of the attnum table. The only way to reorder columns is to either recreate the table or add columns and data until you reach the desired location.

This is pretty weak, but in their defense, in standard SQL, there is no solution for rearranging a column. Database brands that support column order changes determine the extension of SQL syntax.

Another idea comes to me: you can define a VIEW that determines the order of the columns as you like, without changing the physical position of the column in the base table.

Answer 2

This post is old and probably resolved, but I had the same problem. I resolved it by creating a view of the source table with a 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 release the original table and set the name of the new table to the old table.

Answer 3

One, albeit clumsy, option for changing columns, when the column order should be completely changed and foreign keys are used, is to first reset the entire database with data, and then reset only the schema ( pg_dump -s databasename > databasename_schema.sql ) Then edit the schema file to reorder the columns as you would like, and then recreate the database from the schema and finally restore the data to the newly created database.

Answer 4

In PostgreSQL, when you add a field, it will be added at the end of the table. If we need to insert into a specific 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; 

Answer 5

I don't think you can now: see this wiki article .

Three workarounds from this article:

• Restore table
• Add columns and move data
• Hide differences with view.

Answer 6

Open the table in PGAdmin and copy the SQL Create Table statement at the bottom of the SQL panel. Then open the Query Tool and paste. If there is data in the table, change the name of the table to "new_name", if not, delete the comment "-" in the line "Delete table". Edit the column sequence as required. Remember the missing / extra comma in the last column if you move it. Run the new SQL Create Table command. Refresh and ... voila.

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

If the table has data, we also need to change the sequence of data columns. It's simple: use INSERT to import the old table into the new version with:

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

... where c2 , c3 , c1 are the columns c1 , c2 , c3 old table in their new positions. Please note that in this case you must use the "new" name for the edited "old" table, otherwise you will lose your data . If 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 a new list of columns there before copying it to the INSERT .

After making sure everything is in order, DROP old table and change the "new" name to "old" using ALTER TABLE new RENAME TO old; and you're done.

Answer 7

I use Django and it requires an id column in each table if you don't want to have a headache. Unfortunately, I was sloppy, and my bp.geo_location_vague table did not contain this field. I launched a 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 creating a table - the table will be created automatically!)

 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 a big pseudotype in the table. After SELECT * in pg, a bigserial of type bigint will be created.

step 4: Now we can delete the view, delete the original table and rename the new table to the old name. The trick was successfully completed.