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

From: Ian Barwick <barwick(at)gmail(dot)com>
To: PG-General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Unexpected result from selecting an aliased but non-existing column called "name"
Date: 2010-03-09 08:50:45
Message-ID: 1d581afe1003090050q8608a47rb222729becf19ffd@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message dipti shah 2010-03-09 08:53:03 has_schema_privilege function
Previous Message dipti shah 2010-03-09 08:21:38 Re: Is it possible to findout actual owner of table?