Keywords are any reserved SQL terms which have a reserved syntactic meaning to the server. Some common keywords are INSERT, UPDATE,
SELECT, and DELETE.
All SQL commands are keywords, though many keywords themselves are not complete commands. For instance, the command
INSERT INTO is a valid SQL command, and the word INTO is
a reserved keyword. As you might guess, however, the word INTO has no particular
significance when used out of context.
Identifiers, as described earlier, are variable names that reference database objects. These names are arbitrarily
designated by the creator of the database object upon creation. The objects which can be referred to by identifiers in
PostgreSQL may be databases, tables, columns, indices, views, sequences, rules, triggers, or functions.
Example 3-2 adds three pieces of information about Oregon into a simple table called
states.
Example 3-2. Keywords and commands
booktown=#
INSERT INTO states VALUES (33, 'Oregon', 'OR');
INSERT 3389701 1
In Example 3-2, the INSERT INTO SQL
command makes use of the SQL keywords
INSERT, INTO, and
VALUES.
The INSERT INTO command modifies the table referenced by the states identifier. The modification in this case is the insertion of a new
record.
While not normally required, quotes can be used around identifiers, meaning they should be interpreted 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)
The keywords in this statement 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, which uses quotes around the stAtes
identifier, will fail:
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.
Note: 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 expect case to automatically change to uppercase. If you are a developer, and you
are interested in writing easily portable applications, be sure to consider this case issue 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 use uppercase and lowercase terminology carefully. 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, the convention used
throughout this book. By visually separating the fixed, systematic terminology from the user-defined data objects, you make it a great deal easier to quickly read and understand complex SQL statements.
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 SELECT,
DELETE, or UPDATE statements).
If you do not quote an identifier that 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, you would get an error message if 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, as follow:
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 Microsoft Access)
can result in table names with all capitalized letters. Without the functionality of quoted identifiers, you would
have no way to accurately reference these tables.
Both keywords and identifier names in PostgreSQL have a maximum length limit of 31 characters. Parsed keywords or
identifiers over that length limit are automatically truncated. Identifiers may begin with any letter (a through z), or with an underscore,
and may then be followed by letters, numbers (0 through 9), or underscores. While keywords are not permitted to start or end
with an underscore, identifier names
are
permitted to do so. Neither keywords nor identifiers should ever begin with a
number.
In the Section called When quotes are required
" we described how quoted identifiers could be used to "overrule" the case
insensitivity of identifiers by placing quotes around them. The same rule-bending can apply to the assertion that an identifier
cannot begin with a number. While PostgreSQL will not allow you to create a table using the name
1st_bent_rule without quotes, the name is acceptable if it is surrounded with quotes.
Example 3-3 first fails in trying to create an illegally named table. It then proceeds to bend the
rules with quotes.
Example 3-3. Bending rules
booktown=#
CREATE TABLE 1st_bent_rule (rule_name text);
ERROR: parser: parse error at or near "1"
booktown=#
CREATE TABLE "1st_bent_rule" (rule_name text);
CREATE
Furthermore, while quotes themselves are, of course, not allowed within the set of quotes to refer to a table name,
other normally illegal characters are allowed, such as spaces and ampersands. Take note that while
the ANSI/ISO SQL standard forbids using identifiers with the same names as SQL keywords, PostgreSQL (like many other SQL
implementations) has a similarly relaxed view on this, allowing you to force such names with quoted identifiers.
Remember that while the use of quotes can be a useful trick to know for unusual circumstances, if you wish to design portable,
standard SQL statements and relations, it is best to adhere to ANSI/SIO standards whenever possible.
