How to rearrange the column position in PostgreSQL

Question

how to re-arrange the column names in PostgreSQL table with records

entityid formattedfilename 
-------- ----------------- 
1        file1             
2        file2

Re-arrange below format with record

formattedfilename entityid 
----------------- -------- 
file1             1        
file2             2

do you want to rearrange the ordinal position of the columns in the table ? — Vivek S., Feb 27 '15 at 11:26
http://stackoverflow.com/q/23423817/330315 and http://stackoverflow.com/questions/126430 and http://stackoverflow.com/q/1243547/330315 — a_horse_with_no_name, Feb 27 '15 at 11:27

Vivek S. · Accepted Answer · 2015-02-27T13:29:45.083

You can create a VIEW for this,
see following demo

create table foo (entityid int,formattedfilename text);

insert into foo values (1,'file1');
insert into foo values (2,'file2');

select * from foo

RESULT

entityid formattedfilename 
-------- ----------------- 
1        file1             
2        file2

Now create a view like below

create or replace view vfoo as 
select formattedfilename,entityid from foo .

select * from vfoo

RESULT

formattedfilename entityid 
----------------- -------- 
file1             1        
file2             2

Still you want to do it with the table itself then refer : https://wiki.postgresql.org/wiki/Alter_column_position

score -1 · Answer 2 · answered Feb 27 '15 at 10:56

-1

Just select it something like:

SELECT formattedfilename, entityid
FROM mytable

if you want SELECT * should return you that format then i would say you better have a view over your table and query on view.

answered Feb 27 '15 at 10:56

SMA

2 Answers2