Re: How to run queries without double quotes on tables and columns

Susan M Farley <sfarley1(at)gmu(dot)edu> writes:
> Sorry, I'm really new to PostgreSQL and am used to Oracle. In function and in regular queries, I usually have to have double quotes (") around the names of columns and table names. Otherwise, I get an error that the schema, table, or column doesn't exist. I don't always have to though. Sometimes the function works just fine without them even though I am working with the same table and schema. What am I doing wrong?

Names that aren't double-quoted will be folded to lower case. If the
name you've got stored is mixed case or all upper case, you'd have to
quote to get a match. See
http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

particularly the last para under 4.1.1:

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.)

I don't know whether Oracle is exactly standards-compliant on this
point, or has yet another behavior.

regards, tom lane