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'm new to PostgreSQL and I ran into problems using the following query:

SELECT * FROM table1 WHERE ID = 1

ERROR: column "id" does not exist LINE 2: WHERE ID = 1

where ID is a column I added via a GUI-tool.

I then found out that ID is a keyword in my current version of PSQL and should be quoted. But I can't recognize what is this ID for.

On the linked page there is the following notice:

It is important to understand before studying Table C-1 that the fact that a key word is not reserved in PostgreSQL (==true) does not mean that the feature related to the word is not implemented.

So, ID is not marked as a PostgreSQL keyword and there seems to be a feature connected to it, but I can't find out which. SELECT ID itself turns into the same error message as posted above.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

In PostgreSQL, inserting a column named

"ID"

is one thing. Inserting a column named

ID

is another.

create table test (
  "ID" integer not null
);

insert into test values (1);

select *
from test
where ID = 1;

ERROR: column "id" does not exist

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case.

share|improve this answer
    
What I wanted to know is, why the word ID has to be quoted, or which functionality is connected to ID, that I have to quote it for using it as a column name. –  32bitfloat Sep 12 '12 at 15:25
    
It acts as if your GUI tool passed an uppercase column name, including double quotes, to PostgreSQL. (Other things might account for this behavior, but this is the most common one in my experience.) Any time you create a PostgreSQL column name within double quotes, it becomes case-sensitive. In this case, your tool probably created the column "ID" (including the double quotes). PostgreSQL automatically interprets an unquoted column name as lowercase, so in the SELECT query above, it interprets ID as id. And the id column doesn't exist, but "ID" (with quotes) does. –  Mike Sherrill 'Cat Recall' Sep 12 '12 at 15:40
    
Sry I didn't get it, now it's clearer to me. It has nothing to do with possible existing keywords/features, but with case-sensitivity. Thank you! –  32bitfloat Sep 12 '12 at 16:07

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.