Thread: Column Ordering

Ashish Karalkar wrote:
> Hello All,
>
> I am having a table with  5 columns.
> I want to add another column by altering the table at 2nd position
> constraint is that I can not drop and recreate the table as column
> ordering is of importance.
> Is there anyway to do so.

No.  However, you could create a view with the column ordering you want,
after adding the new column.

Sean

--- Sean Davis <sdavis2@mail.nih.gov> wrote:
> > I want to add another column by altering the table at 2nd position
> > Is there anyway to do so.
>
> No.  However, you could create a view with the column ordering you want,
> after adding the new column.

This feature is on the to-do list.  But there isn't any ETA for when we should expect it.  I am
pretty sure it isn't included in version 8.3.

So I guess that this feature could be expected over the next couple of years in versions 8.4 or
8.5(9.0?).

http://www.postgresql.org/docs/faqs.TODO.html#section_8

See bullet:  "Alter" and sub-bullet: "Allow column display reordering ..."

Regards,
Richard Broersma Jr.

Hi all,

Forgive me if I'm wrong but as far as I know, in the relational theory behind the RDBMSs, the colum order in a tupple is irrelevant, like the elements in a set.

Is there any solid reason or need for having colunms ordered ?

Warm regards,

Andres

--- Andres Ledesma <alchir@yahoo.com> wrote:
> Forgive me if I'm wrong but as far as I know, in the relational theory behind the RDBMSs, the
> colum order in a tupple is irrelevant, like the elements in a set.

This is correct.

> Is there any solid reason or need for having colunms ordered ?

My guess for this request is that alot of people would rather write:

SELECT *
  FROM Table;

instead of:

SELECT column1, column5, column2, column3, column4
  from Table;


I've seen alot of "BEST PRACTICE" advice on this list that suggests that writing out all of the
columns is recommended.  However, I am sure that there are alot of individuals that like to write
these queries with the least amount of code possible.

Regards,
Richard Broersma Jr.

> > Forgive me if I'm wrong but as far as I know, in the relational theory behind the RDBMSs, the
> > colum order in a tupple is irrelevant, like the elements in a set.
> > Is there any solid reason or need for having colunms ordered ?
> My guess for this request is that alot of people would rather write:
> SELECT *
>   FROM Table;
> However, I am sure that there are alot of individuals that like to write
> these queries with the least amount of code possible.

It is not just about minimizing code - using "*" is more portable. If
what you actually mean is "all the columns, whatever they be, in the
created order" then this is the way to write it. When you have many
nested queries then adding a column to the source table that can
"cascade" through just by redumping the stored SQL is an enormous
convenience during intermediate development redesigns.

There is actually a codge for reordering columns which I have used in
the past. If used on a live database it should be accompanied by
appropriate transactions and locking. The following code demonstrates
the technique:

-----------------------------
-- Example tables and data --
-----------------------------
CREATE TABLE example1 (id serial PRIMARY KEY, name varchar);
INSERT INTO example1 (id, name) VALUES (1, 'thing');
INSERT INTO example1 (id, name) VALUES (2, 'bit');
INSERT INTO example1 (id, name) VALUES (3, 'stuff');
SELECT setval('example1_id_seq', 3);

CREATE TABLE example2 (
    first serial PRIMARY KEY,
    third integer REFERENCES example1 NOT NULL,
    second text
);
INSERT INTO example2 ("first", third, "second") VALUES (1, 1, 'is');
INSERT INTO example2 ("first", third, "second") VALUES (2, 3, 'that');
INSERT INTO example2 ("first", third, "second") VALUES (3, 2, 'of');
INSERT INTO example2 ("first", third, "second") VALUES (4, 2, 'with');
SELECT setval('example2_first_seq', 4);

SELECT * FROM example2;
 first | third | second
-------+-------+---------
     1 |     1 | is
     2 |     3 | that
     3 |     2 | of
     4 |     2 | with

---------------------------------------------------------
-- Start by adding new columns to the end of the table --
---------------------------------------------------------
ALTER TABLE example2 ADD column third2 integer REFERENCES example1;

--------------------------
-- Copy over the values --
--------------------------
UPDATE example2 SET third2=third;


-----------------------------
-- And any constraints etc --
-----------------------------
ALTER TABLE example2 ADD CONSTRAINT "$3" FOREIGN KEY (third)
REFERENCES example1;

-------------------------------
-- Swap names of old and new --
-------------------------------

ALTER TABLE example2 RENAME COLUMN third TO third1;
ALTER TABLE example2 RENAME COLUMN third2 TO third;

SELECT * FROM example2;
-- first | third1 | second | third
---------+--------+--------+-------
--     1 |      1 | is     |     1
--     2 |      3 | that   |     3
--     3 |      2 | of     |     2
--     4 |      2 | with   |     2

-----------------------
-- Delete old column --
-----------------------
ALTER TABLE example2 DROP COLUMN third1;

SELECT * FROM example2;
-- first | second | third
---------+--------+-------
--     1 | is     |     1
--     2 | that   |     3
--     3 | of     |     2
--     4 | with   |     2



The technique only allows moving columns "to the right", but "moving
left" can be simulated by "moving right" other columns.