Unexpected result from selecting an aliased but non-existing column called "name"

Hi

I was wondering where some spurious information in a query was
coming from - it looked like something was inserting all the
values of a table row as a comma-separated list.

It turns out I was attempting to reference a non-existent column
called (literally) "name", which instead of throwing an error produces
the aforementioned list. This only seems to happen with "name", and
only if it is referenced with the table name or alias.

To reproduce:

test=> CREATE table xx(id int, val text);
CREATE TABLE
test=> INSERT INTO xx values(1,'hello world');
INSERT 0 1
test=> SELECT name FROM xx;
ERROR: column "name" does not exist
LINE 1: SELECT name FROM xx;

test=> SELECT xx.name from xx;
name
-------------------
(1,"hello world")
(1 row)

test=> SELECT xx.foobar FROM xx;
ERROR: column xx.foobar does not exist
LINE 1: SELECT xx.foobar FROM xx;

Reproducible on 8.4.1 and 8.4.2; does not work in 8.3.1
(old test version I happen to have hanging around).

Questions:
- is this a feature?
- and if so, where is it documented?
(given that the key word in this is "name", this is a tricky one
to research).

Thanks for any pointers.

Ian Barwick

On Tuesday 09 March 2010 12:50:45 am Ian Barwick wrote:
> Hi
>
> I was wondering where some spurious information in a query was
> coming from - it looked like something was inserting all the
> values of a table row as a comma-separated list.
>
> It turns out I was attempting to reference a non-existent column
> called (literally) "name", which instead of throwing an error produces
> the aforementioned list. This only seems to happen with "name", and
> only if it is referenced with the table name or alias.
>
>
>
>
> Ian Barwick

See this recent thread for explanation:
http://archives.postgresql.org/pgsql-general/2010-02/msg01038.php

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

Hi Adrian,

2010/3/9 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>:
> On Tuesday 09 March 2010 12:50:45 am Ian Barwick wrote:
>> Hi
>>
>> I was wondering where some spurious information in a query was
>> coming from - it looked like something was inserting all the
>> values of a table row as a comma-separated list.
>>
>> It turns out I was attempting to reference a non-existent column
>> called (literally) "name", which instead of throwing an error produces
>> the aforementioned list. This only seems to happen with "name", and
>> only if it is referenced with the table name or alias.
>>
>> Ian Barwick
>
> See this recent thread for explanation:
> http://archives.postgresql.org/pgsql-general/2010-02/msg01038.php

Thanks for the info, apologies for the delay in replying.

Certainly explains what's going on, though it seems a bit of a "gotcha".

Regards

Ian Barwick