This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
In summary, a SQL statement is comprised of tokens, where each token can represent either a keyword, an identifier, a quoted identifier, a constant, or a special character symbol. Table 3-6 uses a simple SELECT statement to illustrate a basic, but complete, SQL statement and its components.
Table 3-6. A Simple SQL Query
SELECT | id, name | FROM | states | |
---|---|---|---|---|
Token Type | keyword | identifiers | keyword | identifier |
Description | command | id and name columns | clause | table name |
As shown in the table, the SELECT statement contains the keywords SELECT and FROM . The FROM keyword is also called a clause, as it modifies and further describes the SELECT command.
The id , name and states tokens are the identifiers of the statement. The id and name identifiers specify the columns to be selected, while the states identifier specifies the table name to select from. Therefore, with the preceding SQL query, you are instructing PostgreSQL to display the columns named id and name for each row from the states table.
Example 3-13. Example SQL Query
booktown=# SELECT id, name FROM states; id | name ----+------------ 42 | Washington 51 | Oregon (2 rows) booktown=#
Getting more complicated, Table 3-7 and Table 3-8 break down another example statement. This statement uses the UPDATE command, along with SET and WHERE clauses, which respectively specify what to update the records with, and how to find the records to update.
Table 3-7. UPDATE Example: The SET Clause
UPDATE | states | SET | id | = | 51 |
---|---|---|---|---|---|
keyword | identifier | keyword | identifier | operator | integer constant |
command | table name | clause | column | assignment | new id value |
Table 3-8. UPDATE Example: The WHERE Clause
WHERE | name | = | 'Oregon' |
---|---|---|---|
keyword | identifier | operator | string constant |
clause | column name | equivalence | string value to match |
When executed, this statement compares each record's name column to find matches for the WHERE clause's stated condition (equivalence to the string constant 'Oregon'). Then, for each column which matches that condition, it updates the id column with the value 51 .
Breaking it down, this UPDATE statement has three keywords, three identifiers, two operators, and two constants. The keywords are UPDATE (the SQL command), SET , and WHERE (the SQL modifying clauses). The identifiers are the states table name, the id column name, and the name column name.
The operators are both represented by the = operator. When used with the SET clause, this operator is used for assignment (to assign a new value to an existing record's identified column); this is a special use which is unique to the SET clause. In contrast, when used with the WHERE clause, the = operator is used to check equivalence between values. In this case, this means that the equivalence operator will check the value of a record's name column and a string constant with the value of Oregon .
Finally, the constants in this statement are the integer constant 51 (the new value for the id column), and the string constant Oregon (compared to the name column through the WHERE clause).
Example 3-14 therefore updates the states table by setting the id column to 51 wherever the name column matches the value Oregon . It then checks the results of that UPDATE statement with another SELECT statement.
Example 3-14. A SQL Update
booktown=# UPDATE states booktown-# SET id = 51 booktown-# WHERE name = 'Oregon'; UPDATE 1 booktown=# SELECT * FROM states booktown-# WHERE name = 'Oregon'; id | name | abbreviation ----+--------+-------------- 51 | Oregon | OR (1 row)
 
Continue to: