11

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
3
  • It would help to show the definition of site. Apr 17, 2011 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, 2011 at 14:40
  • 1
    Try select site from site - this will help you understand Gaius's answer in more detail Apr 26, 2011 at 9:46

3 Answers 3

11

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.

3
  • Thanks, I didn't know that. What bothers me, if this particular function "name" is documented anywhere?
    – hegemon
    Apr 18, 2011 at 6:06
  • Updated my answer
    – Gaius
    Apr 18, 2011 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) Apr 26, 2011 at 9:44
3

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.

1
  • 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 Jun 13, 2014 at 16:40
0

"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
  • 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. Jan 28, 2013 at 11:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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