CODEWITHSUNDEEP
postgresql
Nusrat Nuriyev
Nusrat Nuriyev

Reputation: 1174

Fully qualified table name in postgreSQL select query

Simple question, however google can't help in reasonable time. Ok, I have user table in my_db database with id column. I want to run very simple query

SELECT id FROM user;

but it fails.

ERROR: column "id" does not exist LINE 1: SELECT id FROM user;

Can you imagine?

Ok, Running 

SELECT * FROM user;

outputs the list of internal postgresql database users, which is nothing to do with my users, it's data from completely another [internal] database. However, connection with my_db was established.

Upvotes: 4

Views: 12333

Answers (2)

user330315
user330315

Reputation:

user is an internal function (and a reserved word) returning the currently logged in user.

To use that as your own identifier, you need to quote it:

select id 
from "user"

or 

select id 
from public."user".

But you should really avoid reserved words as table names (or any name that requires quoting the identifier)

Upvotes: 9

Nusrat Nuriyev
Nusrat Nuriyev

Reputation: 1174

The following query can be rewritten as

SELECT id FROM my_db.public.user;

Where id is column, my_db is database, user is table name, public - is the schema. More about schemas: http://www.postgresql.org/docs/9.1/static/ddl-schemas.html So you don't have to rename the table name.

Upvotes: 5

Related Questions