12

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

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 from Oracle DB is not to use ". 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 error about syntax:

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

Do you know why?

--SOLVED-- Thank you, now I solved the problem! It was about table creation. I created table objects using pgAdminIII and i wrote table name and column names uppercased. pgAdminIII created query with quotas - because of the names was uppercased. So query had to be written with quotas.

veselej
  • 353
  • 2
  • 4
  • 12
  • 1
    Postgres has some peculiar handling of table and field names regarding case-sensitivity. – ypercubeᵀᴹ Jun 10 '11 at 09:20
  • 2
    @ypercube: it's not "peculiar" it's following the SQL standard (and behaves like Oracle, DB2, Firebird, H2, Derby, ...) –  Jun 10 '11 at 09:22
  • 1
    @veselej On a side note: quota = amount of a resource (disk space, cpu utilization etc.) granted to a user; quote = citation, string between quotation marks (', "), so you're really talking about quotes and not quotas here :-) – Frank Schmitt Jun 10 '11 at 09:25
  • 1
    @a_horse_with_no_name: Postgres documentation disagrees with you: ***"The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard"*** – ypercubeᵀᴹ Jun 10 '11 at 09:26
  • 2
    @ypercube: That why I said **behaves** the same. The name resolution with and without quotes still follows the standard (no quotes: case-insesitive, quotes: case-sensitive) –  Jun 10 '11 at 09:28
  • @a_horse: Yes, in that point, yes. As long as one doesn't mix quoted and unquoted names, the behaviour is the same, right. – ypercubeᵀᴹ Jun 10 '11 at 09:33

3 Answers3

14

When you create your tables using double quotes, column and table names become case sensitive. So "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 is the same as CAR_ID (note the missing quotes!)

See the manual for details:

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

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

2

From Postgres documentation :

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
0

Seems to me that the table vc does not have a column named car_id. Are you sure it is there? Do \d vel_car to see the structure of the table.

The quotes are optional and you can usually skip them.

Miki
  • 7,052
  • 2
  • 29
  • 39