Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Keywords and Identifiers

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.

Quoted identifiers

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.

When quotes are required

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.

Identifier validity

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.

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire