Re: select t.name from tbl t (where "name" is not a column name)

Joe,

What PG version are running?

8.2 here complains when running your example:

ERROR:  column foo.name does not exist
LINE 6: select foo.name from foo;
               ^

********** Error **********

ERROR: column foo.name does not exist
SQL state: 42703


Igor Neyman


> -----Original Message-----
> From: Joe Conway [mailto:mail@joeconway.com]
> Sent: Tuesday, February 23, 2010 9:19 PM
> To: pgsql-general@postgresql.org
> Subject: Re: select t.name from tbl t (where "name" is not a
> column name)
>
> On 02/23/2010 05:07 PM, raf wrote:
> > i've just noticed the following behaviour and was wondering
> if there's
> > any documentation to explain what it's for.
> >
> >   create table tbl(id serial primary key, a text, b text, c text);
> >   insert into tbl(a, b, c) values ('abc', 'def', 'ghi');
> >   insert into tbl(a, b, c) values ('jkl', 'mno', 'pqr');
> >   insert into tbl(a, b, c) values ('stu', 'vwx', 'yza');
> >   select t.name from tbl t;
>
> I forget exactly where this is documented (and could not find
> it with a quick look), but calling t.name is the same as
> name(t) if a column reference is not found, and name is a
> function, which it is.
>
> So t.name is essentially casting the whole row as a name
> datatype and outputting the result. Try it with text:
>
> test=# \d foo
>       Table "public.foo"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  f      | integer |
>
> test=# select foo.text from foo;
>  text
> ------
>  (-1)
> (1 row)
>
> test=# drop TABLE foo;
> DROP TABLE
>
> test=# create table foo(f int, text text); CREATE TABLE
>
> test=# insert into foo values(-1,'abc'); INSERT 0 1
>
> test=# select foo.text from foo;
>  text
> ------
>  abc
> (1 row)
>
> test=# select foo.name from foo;
>    name
> ----------
>  (-1,abc)
> (1 row)
>
> HTH,
>
> Joe
>
>