Returning rows in PostgreSQL with a table called "user" I have a table called user

I have some SQL that is select User.* from User

This fails because User is a reserved word however, I was under the impression that Postgresql didn't care about the case of the tables when executing the SQL so I assume there is a conflict because of the reserved word.

In the database the user table is lowercase so if I run this

select * from user

I get a result, although bizarrely I only get one column returned even though I said show all columns.

If I run select * from "user" I get all columns and also SELECT "user".* FROM "user" returns all columns.

Can someone explain what's going on please?

Thanks

(Using PostgreSQL 9.3)
user is a pseudo-function keyword

user is an alias for current_user, a built-in pseudo-function. It doesn't have the () of most zero-argument functions because that's how the SQL standards committee declared that it would be for current_user. I don't know why user follows that too, since I don't think the spec requires it.

Functions can be used in FROM

Anyway, it's legal in PostgreSQL to specify a function in from, e.g.

regress=&gt; SELECT * from clock_timestamp();
        clock_timestamp        
-------------------------------
 2014-09-03 17:00:59.191019+08
(1 row)


because a function that returns a single value can be used in a set-returning context too.

So when you run:

select * from user;


you're really doing:

select current_user;


"quoting" escapes keywords

The reason it "works" if you write "user" instead is that quoted identifiers are always user-identifiers, not keywords. user is a keyword; "user" is a user-defined identifier that's a legal table name.

Confusing, no?

Usually "tablename" and tablename are the same (quoted-lower-case and unquoted-lower-case), unless tablename is a keyword.

This might make more sense if we pick a keyword that isn't a pseudo-function, so it's not legal syntax. Say:

regress=&gt; CREATE TABLE "where" (id integer);
CREATE TABLE

regress=&gt; CREATE TABLE where (id integer);
ERROR:  syntax error at or near "where"
LINE 1: CREATE TABLE where (id integer);
                     ^


Quoting makes identifiers case-sensitive

Normally, PostgreSQL case-folds all identifiers to lower case. This is required by the SQL spec, though it actually requires upper case. So when you:

CREATE TABLE MyTable (id integer);


PostgreSQL actually creates a table named mytable. However, this case-folding is disabled if you "double quote" identifiers - again, per the SQL standard.

So the reason that:

select * from "User";


fails is that PostgreSQL, per the SQL standard, treats quoted identifiers as case-sensitive. So "User", "USER" and "user" are all different things.

How to avoid these problems?

Don't name your tables after keywords like type names, function names, etc. It's often legal, but it's rarely a good idea.

If you're going to use keyword names, you should always "double quote" them wherever they appear - and in fact make a habit of simply doing that for all your identifiers, all the time, and always using consistent case.

There's a keyword list here.