Why can't postgresql execute select statement whose 'where' clause has a key-matching predicate?

Question

select * from note where noteKey = 1

This statement gives me the following error.

ERROR:  column "notekey" does not exist
LINE 1: select * from note where noteKey = 1
                                 ^
HINT:  Perhaps you meant to reference the column "note.noteKey".

I tried note.noteKey, but got the same error. (I used postico by the way, and the noteKey is the primary key of the table note).

What could possibly be wrong with the statement?

You probably created the column with double quotes using `"noteKey"` which is a different column name then `noteKey` or `notekey`. See the manual for details: https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS — , Dec 07 '16 at 22:36
@a_horse_with_no_name, is there a way to know if a field has been created with double quotes, other than write a single select query? — McNets, Dec 07 '16 at 22:41
Check the definition of the table, e.g. the `create table` statement you used. or use `\d note` in `psql` or whatever your SQL client supports. I strongly recommend to **never** use double quotes in SQL. — , Dec 07 '16 at 22:43

score 0 · Answer 1 · answered Dec 07 '16 at 23:00

You need to connect with psql and run \d note.

If that shows the column is called noteKey with an uppercase K then you either have two options

Rename the column ALTER TABLE note RENAME COLUMN "noteKey" TO notekey;
Forever use double quotes " to query it.

In PostgreSQL we never quote column names. This forces them to be caps sensitive. When not quoted, they're automatically lower cased.

This

select * from note where noteKey = 1

Is the same as

select * from note where notekey = 1

Which is different from

select * from note where "noteKey" = 1

Why can't postgresql execute select statement whose 'where' clause has a key-matching predicate?

1 Answers1