4

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.

Improve this question

1 Answer 1

Reset to default
6

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.

Improve this answer
3
  • 1
    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, 2012 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, 2012 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, 2012 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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