Is name a special keyword in PostgreSQL?

Question

I am using Ubuntu and PostgreSql 8.4.9.

Now, for any table in my database, if I do select table_name.name from table_name, it shows a result of concatenated columns for each row, although I don't have any name column in the table. For the tables which have name column, no issue. Any idea why?

My results are like this:

select taggings.name from taggings limit 3;

---------------------------------------------------------------
 (1,4,84,,,PlantCategory,soil_pref_tags,"2010-03-18 00:37:55")
 (2,5,84,,,PlantCategory,soil_pref_tags,"2010-03-18 00:37:55")
 (3,6,84,,,PlantCategory,soil_pref_tags,"2010-03-18 00:37:55")
(3 rows)


select name from taggings limit 3;
ERROR:  column "name" does not exist
LINE 1: select name from taggings limit 3;

Answer 1

This is a known confusing "feature" with a bit of history. Specifically, you could refer to tuples from the table as a whole with the table name, and then appending .name would invoke the name function on them (i.e. it would be interpreted as select name(t) from t).

At some point in the PostgreSQL 9 development, Istr this was cleaned up a bit. You can still do select t from t explicitly to get the rows-as-tuples effect, but you can't apply a function in the same way. So on PostgreSQL 8.4.9, this:

create table t(id serial primary key, value text not null);
insert into t(value) values('foo');
select t.name from t;

produces the bizarre:

  name   
---------
 (1,foo)
(1 row)

but on 9.1.1 produces:

ERROR:  column t.name does not exist
LINE 1: select t.name from t;
               ^

as you would expect.

So, to specifically answer your question: name is a standard type in PostgreSQL (used in the catalogue for table names etc) and also some standard functions to convert things to the name type. It's not actually reserved, just the objects that exist called that, plus some historical strange syntax, made things confusing; and this has been fixed by the developers in recent versions.