PostgreSQL Bugs - BUG #2204: Feature Req: Unique output column names

Author

BUG #2204: Feature Req: Unique output column names

Brandon Black

2006-01-24, 11:24 am

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Alvaro Herrera

2006-01-24, 11:24 am

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)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane

2006-01-24, 11:24 am

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Alvaro Herrera

2006-01-24, 11:24 am

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)

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane

2006-01-24, 11:24 am

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Alvaro Herrera

2006-01-24, 8:24 pm

Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>
> 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)

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Bruce Momjian

2006-01-25, 3:27 am

Alvaro Herrera wrote:
> Tom Lane wrote:
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Brandon Black

2006-01-26, 5:00 pm

On 1/24/06, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> Tom Lane wrote:
>
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links