The only instances where you are required to use quotes are either when a database object's identifier is identical to a keyword, or when the identifier has at least one capitalized letter in its name. In either of these circumstances, you must remember to quote the identifier both when creating the object, as well as in any subsequent references to that object (e.g., in a SELECT , DELETE , or UPDATE statement).
If you do not quote an identifier which is spelled identically to an existing keyword, PostgreSQL will return an error message because it interprets the intended identifier as a keyword . For instance, if you had a table whose name was literally select , and you tried querying it with the following statement:
testdb=# SELECT * FROM select; ERROR: parser: parse error at or near "select"
As you can see, an unquoted query on a table called select produces an error message. To specify that select is in fact a table, and not a keyword, it needs to be placed inside of quotes.
Therefore, the correct syntax to view a table named select is as follows:
testdb=# SELECT * FROM "select"; selected ---------- 0 1 52 105 (4 rows)
Remember that any identifiers with at least one capitalized letter must be treated similarly. For example, if you've for some reason created a table named ProDucts (notice the capitalized "P" and "D"), and you want to destroy it (as you probably should, with a name like that!), then once again the identifier needs to be quoted in order to accurately describe its name to PostgreSQL.
booktown=# DROP TABLE ProDucts; ERROR: table "products" does not exist booktown=# DROP TABLE "ProDucts"; DROP
This technique can be extremely useful in some circumstances, even if you never name database objects with these criteria yourself. For example, importing data through an external ODBC connection (e.g., via MS Access) can result in table names with all capitalized letters. Without the functionality of quoted identifers, you would have no way to accurately reference or affect these tables.