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

user_10040 0
Viewed 196853

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

ALTER TABLE person ALTER COLUMN dob POSITION 37;
alter-table postgresql sql database
9 Answers

"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.

Bill_Karwin_208600

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); ### with all the constraints
    insert into tablename (col1, col2, col3) select col1, col2, col3 from oldtable;
user_20006490

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.
Mike_Woodhouse_10600

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.

user_39034790

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_95064230
user_119212800
Related