PostgreSQL - Query Syntax without Quotation Marks

Question

I have a little silly question. I installed PostgreSQL DB Server, but when I run the query, there is a problem with the column id without quotes. I do not know why quotes are needed around identifiers. My request:

SELECT vc."CAR_ID" FROM "VEL_CAR" vc, "VEL_DRIVER" vd, "VEL_DRIVER_CAR" vdc WHERE vc."CAR_ID" = vdc."CAR_ID" and vdc."DRIVER_ID" = vd."DRIVER_ID"; 

My practice with Oracle DB should not be used. "So, in Oracle:

 SELECT vc.CAR_ID FROM VEL_CAR vc, VEL_DRIVER vd, VEL_DRIVER_CAR vdc WHERE vc.CAR_ID = vdc.CAR_ID and vdc.DRIVER_ID = vd.DRIVER_ID; 

When I run this query without quotes in PostgreSQL, it throws a syntax error:

 ERROR: column vc.car_id does not exist LINE 1: SELECT vc.CAR_ID 

Do you know why?

- solvable - Thanks, now I solved the problem! It was about creating tables. I created the table objects using pgAdminIII, and I wrote the table name and uppercased column names. pgAdminIII created a query with quotas - because the names were in uppercase. Thus, the request should have been written with quotas.

Answer 1

When you create tables using double quotes, the names of columns and tables become case sensitive. Thus, "car_id" is a different name than "car_id"

You need to create your tables without using double quotes, then the names are not case sensitive: car_id same as car_id (note the missing quotes!)

See the manual for more details:

http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Edit:
Oracle behaves exactly the same. The only difference is that Oracle stores the names in upper case and Postgres stores them in lower case. But the behavior when using quotes is identical.

Answer 2

From the Postgres documentation :

Quoting an identifier also makes it case sensitive, while unnamed names always appear in lower case. For example, the identifiers FOO, foo, and "foo" are considered the same PostgreSQL, but "Foo" and "FOO" are different from these three and to each other. (PostgreSQL's undefined lowercase naming is incompatible with the SQL standard, which states that unmentioned names must be uppercase. Thus, foo should be equivalent to "FOO" and not "foo" according to the standard. You If you want to write portable applications, you are advised to always specify a specific name or never quote it.)

Answer 3

It seems to me that the vc table does not have a column named car_id . Are you sure he is? Make \d vel_car to see the structure of the table.

Quotation marks are optional and you can usually skip them.