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;
(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
  • It would help to show the definition of site. – Peter Eisentraut Apr 17 '11 at 12:30
  • ~ It does matter because now we can see that there is no "name" in site to begin with. Why would you query for a column that doesn't exist? – jcolebrand Apr 18 '11 at 14:40
  • 1
    Try select site from site - this will help you understand Gaius's answer in more detail – Jack says try topanswers.xyz Apr 26 '11 at 9:46

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.

  • Thanks, I didn't know that. What bothers me, if this particular function "name" is documented anywhere? – hegemon Apr 18 '11 at 6:06
  • Updated my answer – Gaius Apr 18 '11 at 10:50
  • 2
    More precisely, the row type is being cast to text because that is the input type of the function name. The name function is then converting (not casting) the input string to type name (which will also have the side effect of truncating to 64 bytes) – Jack says try topanswers.xyz Apr 26 '11 at 9:44

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.

  • I don't understand this answer. You're saying the question posed above shouldn't be a problem anymore on 8.3 or higher? Yet the question asks about 9.0 – Colin 't Hart Jun 13 '14 at 16: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

  • 1
    A lot of reserved words can be used, and in this case it quoting doesn't help. This is because if site.name does not exist as a column then, pre-8.3, what would happen is you'd start looking for name functions taking in a site data type or a type implicitly cast from site. Since site could be implicitly cast to text, name(text) would be used. Consequently select site.name from site might be implicitly transformed to select name(site::text) from site which is where the magic comes from. – Chris Travers Jan 28 '13 at 11:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.