Re: Column Ordering - Mailing list pgsql-novice

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