Thread: BUG #2204: Feature Req: Unique output column names
The following bug has been logged online:
Bug reference: 2204
Logged by: Brandon Black
Email address: blblack@gmail.com
PostgreSQL version: All
Operating system: All
Description: Feature Req: Unique output column names
Details:
-------------Example------------
Welcome to psql 8.0.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
blah=# create table foo (x integer, y integer);
CREATE TABLE
blah=# select count(x), count(y) from foo;
count | count
-------+-------
0 | 0
(1 row)
--------------------------------------
Would it be reasonable or possible to have these default output column names
be something unique? count_x and count_y, or "1", "2"? Or even name them
literall "count(x)" and "count(y)". As long as there were some reasonable
convention which doesn't cause conflicting duplicate names...
I realize this is fixable by the user with "AS count_x", this is more an
issue that came in writing cross-vendor ORM modules in perl, where most
other database vendors offer some sort of unique naming convention, but
postgres' output column names are indistinguishable, for the same query.
-- Brandon
Brandon Black wrote: > I realize this is fixable by the user with "AS count_x", this is more an > issue that came in writing cross-vendor ORM modules in perl, where most > other database vendors offer some sort of unique naming convention, but > postgres' output column names are indistinguishable, for the same query. Use the AS clause, which is the one that actually complies with the SQL standard. Or show us where the standard mandates name munging and we will implement it. -- Alvaro Herrera http://www.advogato.org/person/alvherre "La fuerza no está en los medios físicos sino que reside en una voluntad indomable" (Gandhi)
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Or show us where the standard mandates name munging and we
> will implement it.
SQL92 says (in 7.9 <query specification>):
9) Case:
a) If the i-th <derived column> in the <select list> specifies
an <as clause> that contains a <column name> C, then the
<column name> of the i-th column of the result is C.
b) If the i-th <derived column> in the <select list> does not
specify an <as clause> and the <value expression> of that
<derived column> is a single <column reference>, then the
<column name> of the i-th column of the result is C.
c) Otherwise, the <column name> of the i-th column of the <query
specification> is implementation-dependent and different
from the <column name> of any column, other than itself, of
a table referenced by any <table reference> contained in the
SQL-statement.
Rule (c) is a bit interesting since it does NOT say that a generated
name has to be different from the generated names of other columns of
the same SELECT; rather it has to be different from names of columns
in base tables used by the SELECT. Not sure why they did that. We
don't really honor this rule at present.
In any case, rules (a) and (b) *directly* contradict the notion that
output column names can be guaranteed unique.
Also notice that generated names are "implementation-dependent" not
"implementation-defined" --- that means that we do not have to document
what the generation process is, nor promise that it won't change.
This means that any application that depends on generated names to be
particular things is violating the spec, and has only itself to blame
when it breaks. You should be using AS if you want stable names to
refer to these columns with.
I haven't looked at SQL99 or SQL2003, but most likely they say the same
thing in two or three times as many words ;-)
regards, tom lane
Tom Lane wrote: > I haven't looked at SQL99 or SQL2003, but most likely they say the same > thing in two or three times as many words ;-) Thanks for the pointer. In SQL 2003, this is 7.12 <query specification>: 17) Case: a) If the i-th <derived column> in the <select list> specifies an <as clause> that contains a <column name> CN, then the <column name> of the i-th column of the result is CN. b) If the i-th <derived column> in the <select list> does not specify an <as clause> and the <value expression> of that <derived column> is a single column reference, then the <column name> of the i-th column of the result is the <column name> of the column designated by the column reference. c) Otherwise, the <column name> of the i-th column of the <query specification> is implementation dependent. So they actually simplified the third rule and we comply with it! It would be nice to have this text in HTML or info format. This PDF mess is really awkward. -- Alvaro Herrera http://www.PlanetPostgreSQL.org "We are who we choose to be", sang the goldfinch when the sun is high (Sandman)
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> It would be nice to have this text in HTML or info format. This PDF
> mess is really awkward.
Yeah, that's why I tend to look at SQL92 or SQL99 first ... I have those
drafts in plain-ASCII format, which is so much easier to grep and copy.
Would love to lay my hands on plain-ASCII SQL2003.
regards, tom lane
Tom Lane wrote: > Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > > It would be nice to have this text in HTML or info format. This PDF > > mess is really awkward. > > Yeah, that's why I tend to look at SQL92 or SQL99 first ... I have those > drafts in plain-ASCII format, which is so much easier to grep and copy. > Would love to lay my hands on plain-ASCII SQL2003. What I use is the text version I extracting using pdf2text (or something like that). While the formatting is very awkward, it is at least greppable. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 The easiest way to resolve [trivial code guidelines disputes] is to fire one or both of the people involved. (Damian Conway)
Alvaro Herrera wrote: > Tom Lane wrote: > > Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > > > It would be nice to have this text in HTML or info format. This PDF > > > mess is really awkward. > > > > Yeah, that's why I tend to look at SQL92 or SQL99 first ... I have those > > drafts in plain-ASCII format, which is so much easier to grep and copy. > > Would love to lay my hands on plain-ASCII SQL2003. > > What I use is the text version I extracting using pdf2text (or something > like that). While the formatting is very awkward, it is at least > greppable. I can export text from Acrobat on XP. Have you tried that? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On 1/24/06, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: > Tom Lane wrote: > > > I haven't looked at SQL99 or SQL2003, but most likely they say the same > > thing in two or three times as many words ;-) > > Thanks for the pointer. In SQL 2003, this is 7.12 > <query specification>: > > 17) Case: > a) If the i-th <derived column> in the <select list> specifies an > <as clause> that contains a <column name> CN, then the <column name> > of the i-th column of the result is CN. > b) If the i-th <derived column> in the <select list> does not specify an > <as clause> and the <value expression> of that <derived column> is a > single column reference, then the <column name> of the i-th column of > the result is the <column name> of the column designated by the > column reference. > c) Otherwise, the <column name> of the i-th column of the <query > specification> is implementation dependent. > > So they actually simplified the third rule and we comply with it! > > > It would be nice to have this text in HTML or info format. This PDF > mess is really awkward. Well, then the current behavior is kosher in SQL2003 then, but not neccesarily for SQL92 if someone decided to define some literal column names like "count" or "sum" :) It's not a big deal, just seemed that in certain situations the way other vendors have done it ( "count(x)", "count(y)" ... or .. "1", "2") at least avoid duplication in common cases. -- Brandon