Обсуждение: Add Column BEFORE/AFTER another column
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? 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? TIA, Matthew
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