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

Sorry, I'm really new to PostgreSQL and am used to Oracle. In function and in regular queries, I usually have to have
doublequotes (") around the names of columns and table names. Otherwise, I get an error that the schema, table, or
columndoesn't exist. I don't always have to though. Sometimes the function works just fine without them even though I
amworking with the same table and schema. What am I doing wrong? 

Thank you,
Susan

If your object (table, column, view etc) have mixed-case names (e.g. My_Table instead of my_table), you need to quote them.  If you have a table called My_Table but use [SELECT * FROM My_Table;] it won't match as it is treated as if it is all lower-case unless you quote it.  In that case, you'd have to use [SELECT * FROM "My_Table";]. The same applies to any database object.

Susan M Farley <sfarley1@gmu.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
doublequotes (") around the names of columns and table names. Otherwise, I get an error that the schema, table, or
columndoesn't exist. I don't always have to though. Sometimes the function works just fine without them even though I
amworking 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

Tom Lane wrote on 11.10.2009 04:42:
> I don't know whether Oracle is exactly standards-compliant on this
> point, or has yet another behavior.
>
It is. It folds everything to uppercase thus being closer to the standard than PG ;)

Thomas