why table.name is translated to (name.*)::name?

2010/3/30 Pavel Stehule <pavel.stehule@gmail.com>:

Hello,

I was noticed on little bit strange feature of PostgreSQL 8.4 and 9.0

we can use a non existing column "name". What does mean?

postgres=# create table h(a int, b int);
CREATE TABLE
Time: 2,604 ms
postgres=# insert into h values(199,22);
INSERT 0 1
Time: 0,970 ms
postgres=# explain verbose select h.name from h;
                        QUERY PLAN
-------------------------------------------------------------
 Seq Scan on public.h  (cost=0.00..42.10 rows=2140 width=32)
  Output: (h.*)::name
(2 rows)

Time: 0,727 ms
postgres=# select h.name from h;
  name
----------
 (199,22)
(1 row)

Time: 0,589 ms
postgres=#

FYI this has caused me (and presumably a few other people) a bit of
head-scratching, e.g.:

http://archives.postgresql.org/pgsql-general/2010-03/msg00362.php

I imagine it has some potential as a 'gotcha', as "name" is hardly an
uncommon column name, but it's not an issue which can easily
researched...

Ian Barwick

Ian Barwick <barwick@gmail.com> writes:

2010/3/30 Pavel Stehule <pavel.stehule@gmail.com>:

we can use a non existing column "name". What does mean?

FYI this has caused me (and presumably a few other people) a bit of
head-scratching, e.g.:
http://archives.postgresql.org/pgsql-general/2010-03/msg00362.php

We could make that stop happening if we were willing to restrict the
cases in which an I/O conversion would be applied, but I think the cure
might be worse than the disease. It would be an entirely arbitrary
restriction of a feature.

regards, tom lane

2010/3/30 Tom Lane <tgl@sss.pgh.pa.us>:

Ian Barwick <barwick@gmail.com> writes:

2010/3/30 Pavel Stehule <pavel.stehule@gmail.com>:

we can use a non existing column "name". What does mean?

FYI this has caused me (and presumably a few other people) a bit of
head-scratching, e.g.:
  http://archives.postgresql.org/pgsql-general/2010-03/msg00362.php

We could make that stop happening if we were willing to restrict the
cases in which an I/O conversion would be applied, but I think the cure
might be worse than the disease.  It would be an entirely arbitrary
restriction of a feature.

it is confusing :(. It returns some data, but it have to returns syntax error.

Regards
Pavel Stehule