While not normally required, identifiers can be written inside of quotes, which causes their case to be interpretted literally. For example, if we want to view each of the columns from a table called states , a simple statement to achieve this would ordinarily read:
booktown=# SELECT * FROM states; id | name | abbreviation ----+------------+-------------- 33 | Oregon | OR 42 | Washington | WA (2 rows)
Examining this statement, the keywords are SELECT and FROM , while the identifiers are the asterisk * (indicating all columns), and states (the table name). With this command, we are selecting all columns from a table named states and thereby viewing its contents.
You can accomplish the same thing by putting quotes around the identifier, with the following statement:
booktown=# SELECT * FROM "states"; id | name | abbreviation ----+------------+-------------- 33 | Oregon | OR 42 | Washington | WA (2 rows)
As you can see, the output is identical when applying quotes to a lowercase identifier. However, the following statement will fail if you use quotes around the stAtEs identifier:
booktown=# SELECT * FROM "stAtEs"; ERROR: Relation 'stAtEs' does not exist
This statement fails because it instructs PostgreSQL to look for a table called, literally, stAtEs (rather than states ). In other words, with the use of quotes, the statement has explicitly requested that PostgreSQL interpret the identifier name literally .
All non-quoted identifiers are folded , or converted, to lowercase. When specifying stAtEs , or STATES (i.e., any combination of uppercase or lowercase letters) without quotes, PostgreSQL automatically converts the identifier to lowercase ( states ) before processing the statement.
The folding of unquoted identifiers to lowercase names is a PostgreSQL-specific convention. The SQL92 standard specifies that unquoted identifiers always be converted to uppercase. For both legacy and readability reasons, PostgreSQL does not intend to move to this part of the SQL92 standard.
This should be of special note to database administrators familiar with other SQL products, such as Oracle, who may be used to case automatically being folded to uppercase. If you are a developer, and you are interested in writing easily portable applications, be sure to take this case-issue into consideration to avoid conflicts over this convention.
Since the parser can still read and understand mixed-case statements (provided that they are formed with the correct syntax), you should exercise care with your use of uppercase and lowercase terminology. Your use of case can both help and hinder your efficiency when working with a large amount of SQL.
We recommend that, for readability, you try typing identifiers in lowercase, and keywords in uppercase, as is used throughout this book. By visually separating the fixed, systematic terminology from the user-defined data objects, it can be a great deal easier for you to quickly read and understand complex SQL statements.