Where does the magic column "name" come from?

Question

I got this by accident:

db=> select name from site;
ERROR:  column "name" does not exist
LINE 1: select name from site;
               ^
db=> select site.name from site;
     name
---------------
 (1,mysitename)
(1 row)

The second query return a tuple containing a whole row. Using postgres 9.0.1.

Edit: the definition of site by request. I doesn't really matter, this quirk works for any table.

db=> \d site
                         Table "public.site"
 Column |  Type   |                     Modifiers
--------+---------+---------------------------------------------------
 id     | integer | not null default nextval('site_id_seq'::regclass)
 title  | text    | not null

Gaius · Accepted Answer · 2011-04-18T10:50:31.640

NAME is actually a function. It's a quirk of Postgres that a function with one argument e.g. function(arg) can also be called as arg.function. From the docs:

The equivalence between functional notation and attribute notation makes it possible to use functions on composite types to emulate "computed fields".

NAME is an internal type for object names, and this function is casting its argument to that type and returning it.

score 3 · Answer 2 · answered Aug 31 '12 at 01:37

Also note that the implicit cast to name was removed in PostgreSQL 8.3, which means this behavior no longer works. It is virtually impossible to accidently get this behavior in PostgreSQL 8.3 and higher because tuples don't automatically convert to text.

So in 9.1:

or_examples=# select c.name from comp_table_test c;
ERROR:  column c.name does not exist
LINE 1: select c.name from comp_table_test c;

but to get that behavior we have to:

or_examples=# select name(c::text) from comp_table_test c;

Or we could define our own name function taking in type comp_table_test and returning whatever we'd like.

score 0 · Answer 3 · answered Apr 17 '11 at 17:40

"name" is a reserved key word. So you should "quote" the keyword to use it:

SELECT "name" FROM site;

This has resolved some of these issues for me in the past, altough the code you posted should also work without quoting. On the other hand

select site.name from site;

word because you are explicitly using the schema to resolve the name of the column

Where does the magic column "name" come from?

3 Answers3