Обсуждение: why table.name is translated to (name.*)::name?
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=# Regards Pavel Stehule
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 > regards, tom lane >