Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
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
    
Try select site from site - this will help you understand Gaius's answer in more detail –  Jack Douglas Apr 26 '11 at 9:46

3 Answers 3

up vote 10 down vote accepted

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.

share|improve this answer
    
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
    
Thanks, accepted! –  hegemon Apr 18 '11 at 20:17
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 Douglas 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.

share|improve this answer
    
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 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

share|improve this answer
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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