Reorder columns in a table?

Top  Previous  Next

Unfortunately this cannot be accomplished with any version of Postgresql using SQL commands.

(at least not yet)

 

There is a work around though and it involves creating a temp table and a insert/select statement to copy the data from the

original table to the new table with the new column order.

 

For example say we have a existing table:

 

CREATE TABLE public.access

(

aid serial NOT NULL,

mask varchar(255) NOT NULL ,

type varchar(255) NOT NULL ,

status smallint NOT NULL ,

CONSTRAINT access_pkey PRIMARY KEY (aid)

)WITHOUT OIDS;

 

 

The first step is to create  another table with the new column order:

 

CREATE TABLE public.access_temp

(

aid serial NOT NULL,

mask varchar(255) NOT NULL ,

status smallint NOT NULL,

type varchar(255) NOT NULL,

CONSTRAINT access_pkey1 PRIMARY KEY (aid)

)WITHOUT OIDS;

 

Copy the data from the original table to the new one

 

INSERT into public.acces_temp

SELECT

aid,

mask,

status,

type

FROM public.access;

 

Then drop the original table and rename the temp one to the original name.

 

Make sure your save the create statements for triggers, comments etc as you will lose these when the original table is dropped.

You can copy and past these from the table editors ddl tab.