Add Column BEFORE/AFTER another column

In response to Matthew <mboehm@voilaip.com>:

Hi gang,
In MySQL it is possible to add a column before/after another column. I
have not been able to find such syntax in the Postgres manual. Does this
ability exist?

It does not.

Is my only solution to create a new table with the new column, copy the
data, delete the old table and rename the new one?

The fact that you're asking this question is a hint that you're using
unsafe coding practices that may bite you at some point. The order
of columns is not guaranteed to be the same as when you do CREATE
TABLE, it's just coincidence that they usually are.

To get your columns in a specific order, specify the column names in
that order in your SELECT statement. The SQL standard doesn't provide
for any other way to guarantee column order, and neither does Postgres.

--
Bill Moran
http://www.potentialtech.com

Hey Bill,

It does not.

Bummer.

To get your columns in a specific order, specify the column names in
that order in your SELECT statement. The SQL standard doesn't provide
for any other way to guarantee column order, and neither does Postgres.

Yes, I realize this and we do identify our columns during select
statements, but when you look at a table using a tool like phpPGAdmin or
pgAdmin3, the columns are displayed in some defined order. It's much
easier to see your data/columns in some logical order (ie: all the cost
columns next to each other).

Thanks,
Matthew

On Aug 24, 2007, at 2:18 PM, Matthew wrote:

Hey Bill,

It does not.

Bummer.

To get your columns in a specific order, specify the column names in
that order in your SELECT statement. The SQL standard doesn't
provide
for any other way to guarantee column order, and neither does
Postgres.

Yes, I realize this and we do identify our columns during select
statements, but when you look at a table using a tool like
phpPGAdmin or
pgAdmin3, the columns are displayed in some defined order. It's much
easier to see your data/columns in some logical order (ie: all the
cost
columns next to each other).

Using a view might give you what you're looking for:

abacus=# select * from access_role;
id | name
----+-------
1 | user
2 | admin
(2 rows)

abacus=# create view bass_ackwards as select name, id from
access_role;
CREATE VIEW
abacus=# select * from bass_ackwards;
name | id
-------+----
user | 1
admin | 2
(2 rows)

Cheers,
Steve

On Friday 24 August 2007 17:18, Matthew wrote:

Hey Bill,

It does not.

Bummer.

To get your columns in a specific order, specify the column names in
that order in your SELECT statement. The SQL standard doesn't provide
for any other way to guarantee column order, and neither does Postgres.

Yes, I realize this and we do identify our columns during select
statements, but when you look at a table using a tool like phpPGAdmin or
pgAdmin3, the columns are displayed in some defined order. It's much
easier to see your data/columns in some logical order (ie: all the cost
columns next to each other).

FWIW (and it isnt much) we had the semblence of a patch and an actual plan for
implementing this, but no one got interested enough to finish it for 8.3.
Should you happen to know an ambituous C hacker, there is a good chance it
could be included in 8.4.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

On Aug 24, 2007, at 4:18 PM, Matthew wrote:

Hey Bill,

It does not.

Bummer.

To get your columns in a specific order, specify the column names in
that order in your SELECT statement. The SQL standard doesn't
provide
for any other way to guarantee column order, and neither does
Postgres.

Yes, I realize this and we do identify our columns during select
statements, but when you look at a table using a tool like
phpPGAdmin or
pgAdmin3, the columns are displayed in some defined order. It's much
easier to see your data/columns in some logical order (ie: all the
cost
columns next to each other).

PHP, eh? Do you know it? Given that Postgres doesn't provide that
convenience, perhaps you could add the functionality to phpPGAdmin.
It is open source. If you're not up on PHP or, well, just don't feel
like it, make a feature request there. Believe me, it's already been
made here. The usual answer, or argument against, is because the
standard dictates that the order of attributes in rows returned by
queries is undefined in the absence a specified (in the query) ordering.

Although it occurs to me that, while that answer is correct and
justified, that's not the answer to what most people actually want.
What they want is that when they look at a table they see a
particular order. Has anybody suggested or discussed adding an
optional view, or description, ordering table meta-attribute?

On 8/24/07, Robert Treat <xzilla@users.sourceforge.net> wrote:

On Friday 24 August 2007 17:18, Matthew wrote:

Hey Bill,

It does not.

Bummer.

To get your columns in a specific order, specify the column names in
that order in your SELECT statement. The SQL standard doesn't provide
for any other way to guarantee column order, and neither does Postgres.

Yes, I realize this and we do identify our columns during select
statements, but when you look at a table using a tool like phpPGAdmin or
pgAdmin3, the columns are displayed in some defined order. It's much
easier to see your data/columns in some logical order (ie: all the cost
columns next to each other).

FWIW (and it isnt much) we had the semblence of a patch and an actual plan for
implementing this, but no one got interested enough to finish it for 8.3.
Should you happen to know an ambituous C hacker, there is a good chance it
could be included in 8.4.

really? i don't remember it? can you point me where that patch is?, i
can't find it, either

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook

Erik Jones <erik@myemma.com> writes:

... The usual answer, or argument against, is because the
standard dictates that the order of attributes in rows returned by
queries is undefined in the absence a specified (in the query) ordering.

I don't think this is true. The spec is explicit that *row* order is
indeterminate without an ORDER BY clause, eg SQL92 section 20.2 GR4:

4) If an <order by clause> is not specified, then the ordering of
the rows of Q is implementation-dependent.

However, *column* order seems always respected, eg 7.9 rule 3 saith
about SELECT * :

b) Otherwise, the <select list> "*" is equivalent to a <value
expression> sequence in which each <value expression> is a
<column reference> that references a column of T and each
column of T is referenced exactly once. The columns are ref-
erenced in the ascending sequence of their ordinal position
within T.

Another example is that section 11.11 is crystal clear that ALTER TABLE
ADD COLUMN puts the new column at the end:

4) In all other respects, the specification of a <column
definition> in an <alter table statement> has the same effect
as specification of the <column definition> in the <table
definition> for T would have had. In particular, the degree of T
is increased by 1 (one) and the ordinal position of that column
is equal to the new degree of T as specified in the General
Rules of Subclause 11.4, "<column definition>".

AFAIK the convention that "using SELECT * in code is a bad idea" is
folklore born from years of maintaining real applications; it's surely
good advice, but it's not grounded in any vagueness of the spec.

As far as real PG plans go, I've lost track of the number of requests
we've had for the ability to adjust column order. To do this in a
reasonably sane fashion, we have to separate logical from physical
column order; and the reason that's not been tackled is fear of bugs
from using the wrong kind of column number in the wrong place. It'll
probably get done someday, but my advice would be to stay away from
the first major release after it happens ...

regards, tom lane

On Saturday 25 August 2007 01:40, Jaime Casanova wrote:

On 8/24/07, Robert Treat <xzilla@users.sourceforge.net> wrote:

On Friday 24 August 2007 17:18, Matthew wrote:

Hey Bill,

It does not.

Bummer.

To get your columns in a specific order, specify the column names in
that order in your SELECT statement. The SQL standard doesn't
provide for any other way to guarantee column order, and neither does
Postgres.

Yes, I realize this and we do identify our columns during select
statements, but when you look at a table using a tool like phpPGAdmin
or pgAdmin3, the columns are displayed in some defined order. It's
much easier to see your data/columns in some logical order (ie: all the
cost columns next to each other).

FWIW (and it isnt much) we had the semblence of a patch and an actual
plan for implementing this, but no one got interested enough to finish it
for 8.3. Should you happen to know an ambituous C hacker, there is a good
chance it could be included in 8.4.

really? i don't remember it? can you point me where that patch is?, i
can't find it, either

Hmm... looking back at the most recent discussion, I'm not sure a patch was
ever put forward. The most recent discussion started at
http://archives.postgresql.org/pgsql-hackers/2007-02/msg01138.php
though the author eventually was dissuaded,
http://archives.postgresql.org/pgsql-hackers/2007-02/msg01272.php
My suspicion is that someone will to do both parts (physical optimization and
logical ordering) could probably get some help from him though.
It's worth noting that if you dig around, I think you'll find another post on
hackers discussing having three numbers (physical storage pos, logical
position, and unique identifer) to maintain all the column bits. It'd be
worth asking again on hackers once the above was reviewed before too much
coding went on.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL