Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL - query syntax without quotes

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.

like image 797
veselej Avatar asked Jun 10 '11 09:06

veselej


People also ask

How do I escape quotes in PostgreSQL?

PostgreSQL also accepts “escape” string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g., E'foo' .

How do I ignore special characters in PostgreSQL?

Normally single and double quotes are commonly used with any text data in PostgreSQL. To ignore or escape the single quote is a common requirement of all database developers. By using double quotes and backslash we can avoid the complexity of single quotes as well as it is easy to read and maintain.

How do I escape double quotes in PostgreSQL?

> Quotes and double quotes should be escaped using \.

How escape double quotes in SQL query?

If you need to use the double quote inside the string, you can use the backslash character. Notice how the backslash in the second line is used to escape the double quote characters. And the single quote can be used without a backslash.


1 Answers

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.

like image 184
a_horse_with_no_name Avatar answered Oct 02 '22 10:10

a_horse_with_no_name