Conceptual information on relational databases and tables is of course entirely moot if you don't have any concept of how to directly interact with your data. From a general perspective, SQL consists entirely of structured statements, with which all data in the database is added, modified and removed. These statements form the basis for your communication with the PostgreSQL server.
The following sections will dissect the anatomy of a SQL statement into its structural pieces, explaining the significance of each, and their relation to one another. When example PostgreSQL statement results are displayed, the output is taken from the standard PostgreSQL command-line client, psql.
Our SQL examples will commonly take place within an example database called booktown, the database for our imaginary book store, Book Town. The output from psql will consistently be prefixed with a default prompt style, which looks like this for our booktown examples:
Some simpler examples may take place in our generic test database, called testdb, if not specific to the Book Town examples. By default, the psql prompt displays only the name of the connected database, and the =# characters indicating that the system is ready for a new command (though you will see that the = symbol will change dynamically as psql tracks the status of SQL input). We display this prompt along with the SQL input and output in order to help familiarize you with the psql output.
Chapter 4 documents psql in more detail, and it is only mentioned here to explain the source and style of this book's SQL examples using PostgreSQL.
The schema (with sample data) for the booktown database can be found in the booktown.sql file, on the included CD-Rom. To install this database from scratch, type psql -f /mnt/cdrom/booktown.sql from the command line (where /mnt/cdrom is the path to your mounted CD).
SQL statements always begin with a command, (a word, or group of words, which describes what action the statement will initiate). The command can be called the verb of the SQL statement, as it always describes an action to be taken. Statements typically contain one or more clauses, which are formal modifiers which further describe the function of the SQL statement.
Table 3-2 contains a list of some of the most commonly used PostgreSQL commands.
Table 3-2. Fundamental PostgreSQL Commands
Creates a new database
Creates a new index on a table column
Creates a new sequence in an existing database
Creates a new table in an existing database
Creates a new trigger definition
Creates a new view on an existing table
Retrieves records from a table
Adds one or more new records into a table
Modifies the data in existing table records
Removes existing records from a table
Destroys an existing database
Removes a column index from an existing table
Destroys an existing sequence generator
Destroys an existing table
Destroys an existing trigger definition
Destroys an existing table view
Adds a new PostgreSQL user account to the system
Modifies an existing PostgreSQL user account
Removes an existing PostgreSQL user account
Grant rights on a database object to a user
Deny rights on a database object from a user
Creates a new SQL function within a database
Creates a new language definition within a database
Creates a new SQL operator within a database
Creates a new SQL data type within a database
While obviously code-like in nature, SQL was designed with ease of use and readability in mind. As a result, SQL statements often bear a strong resemblance to simple, instructional English sentences. A strong feature of SQL is that its statements are designed to instruct the server what data to find, not literally how to find it, as you would be forced to in an ordinary programming language. Reading a well-designed SQL query should be nearly as easy as reading an ordinary sentence.
In SQL texts, the word query is frequently used interchangeably with statement. In order to be clear, within this book the term query is used only to refer to statements which return data (e.g., SELECT statements), rather than a general SQL statement which may instead create, add, or modify data.
Internally, PostgreSQL interprets structured SQL statements as a sequence of tokens, usually delimited by whitespace (spaces or newlines, outside of quotes), though some tokens may be placed adjacently if there is no chance of ambiguity (such as when operators are placed directly next to identifiers). A token in this context is a word or character which can be identified meaningfully by the server when the SQL statement is parsed, or interpreted.
Technically, each token can either be considered a keyword, an identifier, a quoted identifier, a constant (also called a literal), or one of several special character symbols. Keywords are words PostgreSQL recognizes as a word with a pre-defined SQL or PostgreSQL-specific meaning; these include SQL commands, clauses, function names, and special noise terms which are often accompanied optionally with SQL commands (e.g., the noise term WORK in the COMMIT command). In contrast, identifiers represent variable names for tables, columns, and any other database object.
Both keywords and identifiers are essentially references to an internally defined function, value or record as far as PostgreSQL is concerned. A constant, on the other hand, describes a piece of data that is interpreted literally, such as a number or character string.
Finally, a SQL statement will contain special character symbols. These are reserved characters (such as the parentheses, semi-colon, and square brackets) which logically affect the meaning and arrangement of your keywords, identifiers and literals. You can think of these characters as the punctuation for your SQL statement. Operators fall under the category of special character symbols. Operators can be used to imply logical operations or evaluations between data values (either literals, or represented by identifiers), and are generally between one and four characters in length.
The following sections explain and expand upon the nature of these elementary components of SQL.
As described in the preceding section, each sequential element of a SQL statement is considered a token. What may not be immediately clear, however, is that tokens may be kept all on the same line, or they may be split across several lines, as extra whitespace is ignored by PostgreSQL's parser.
Consider the SQL statement in Example 3-1, which is executed first on a single line, and then executed again, split across two separate lines. Both SELECT statements instruct the database to display the entire contents of the my_list table:
Example 3-1. Spaces and Newlines
testdb=# SELECT * FROM my_list; todos ------------------------------------------------ Pick up laundry. Send out bills. Wrap up Grand Unifying Theory for publication. (3 rows) testdb=# SELECT * testdb-# FROM testdb-# my_list; todos ------------------------------------------------ Pick up laundry. Send out bills. Wrap up Grand Unifying Theory for publication. (3 rows)
In Example 3-1 there are several newlines and spaces between the second statement's tokens. As you can see by the identical output, PostgreSQL ignores the extra newlines and spaces, making both statements semantically equivalent. You can take advantage of this behavior by splitting a long string of tokens across numerous lines for improved readability of your SQL statement. This probably isn't necessary for statements as simple as those in Example 3-1, but it can be quite helpful when dealing with complex SQL statements with numerous clauses, expressions and conditions. Throughout this book we will periodically split some statements over several lines to help show what each part of the statement is intended to accomplish.
Keywords are any reserved SQL terms which have a reserved syntactic meaning to the server. Some common examples of 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 which 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 examining the statement in Example 3-2, you will see the use of the SQL keywords INSERT, INTO, and VALUES, as illustrated via the INSERT INTO SQL command.
The table referenced by the states identifier is what is then modified by the INSERT INTO command. The modification in this case is the insertion of a new record.
While not normally required, identifiers can be written inside of quotes, which causes their case to 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)
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, 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.
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.
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 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), and underscores. While keywords are not permitted to start or end with an underscore, identifier names are. Neither keywords nor identifiers should ever begin with a number.
the section called When quotes are required described how quoted identifiers could be used to "over-rule" 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.
While much of the data in working with a database is stored on the disk and referred to via identifiers (e.g., table names, column names, and functions), there are obviously times when new data must be introduced to the system. This may be observed when inserting new records, when forming clauses to specify criteria to delete or modify, or even when performing calculations on existing records. This data is input through constants, which are sometimes called literals, because they literally represent a value in a SQL statement (rather than referencing an existing value by identifier).
An implicitly typed constant is one whose type is recognized automatically by PostgreSQL's parser merely by its syntax. PostgreSQL supports five types of implicitly typed constants:
Bit string constants
Floating point constants
A string constant is an arbitrary sequence of characters bound by single quotes (apostrophes). These are typically used when inserting character data into a table, or when passing character data to any other database object. A practical example of the necessity of string constants is updating the first and last names of authors in Book Town's authors table.
Consider a simple authors table, which correlates an author's first name, last name, and a numeric author identifier.
booktown=# SELECT * FROM authors; id | last_name | first_name -------+-----------+------------------ 1809 | Geisel | Theodor Seuss 1111 | Denham | Ariel 15990 | Bourgeois | Paulette 25041 | Bianco | Margery Williams 16 | Alcott | Luoisa May 115 | Poe | Edgar Allen (6 rows)
Looking at this table's contents, it might stand out to you that the first_name with id 16, "Louisa May" has been misspelled as "Luoisa May." To correct this, an UPDATE statement can be made with a string constant, as shown in Example 3-4.
Example 3-4. Using String Constants
booktown=# UPDATE authors booktown-# SET first_name = 'Louisa May' booktown-# WHERE first_name = 'Luoisa May'; UPDATE 1 booktown=# SELECT * FROM authors; id | last_name | first_name -------+-----------+------------------ 1809 | Geisel | Theodor Seuss 1111 | Denham | Ariel 15990 | Bourgeois | Paulette 25041 | Bianco | Margery Williams 15 | Poe | Edgar Allen 16 | Alcott | Louisa May (6 rows)
The UPDATE statement made in Example 3-4 uses the string constants 'Louisa May' and 'Luoisa May' in conjunction with the SET and WHERE keywords. This statement updates the contents of the table referenced by the authors identifier, and as shown, corrects the misspelling.
The fact that string constants are bound by single quotes presents an obvious semantic problem, however, in that if the sequence itself contains a single quote, the literal bounds of the constant are made ambiguous. To escape (make literal) a single quote within the string, you may type two adjacent single quotes. The parser will interpret the two adjacent single quotes within the string constant as a single, literal single quote. PostgreSQL will also allow single quotes to be embedded by using a C-style backslash:
testdb=# SELECT 'PostgreSQL''s great!' AS example; example --------------------- PostgreSQL's great! (1 row)
booktown=# SELECT 'PostgreSQL\'s C-style slashes are great!' AS example; example ----------------------------------------- PostgreSQL's C-style slashes are great! (1 row)
PostgreSQL also supports the C-style backslash escape sequences which are listed in Table 3-3
Table 3-3. PostgreSQL Supported C-Style Escape Sequences
A literal backslash
A literal apostrophe
A form feed
A carriage return
An ASCII character with the corresponding octal number xxx
As a result of the backslashes's special meaning described in Table 3-3, in order to include a backslash in the string you must escape it using a another backslash (e.g., 'A single backslash is: \\' will transform the pair of backslashes into a single backslash).
When entering two quoted character strings to PostgreSQL that are separated by some amount of whitespace, and where that whitespace includes at least one newline, the strings are concatenated and viewed as if they had been typed as one constant. This is illustrated in Example 3-5
Example 3-5. Multi-line String Constants
booktown=# SELECT 'book' booktown-# booktown-# 'end' AS example; example --------- bookend (1 row) booktown=# SELECT 'bookend' AS example; example --------- bookend (1 row)
As you can see, the semantics of the two statements is equivalent. However, at least one new-line is required for this interpretation to be possible, as spaces alone would result in the following error.
booktown=# SELECT 'book' 'end' AS mistake; ERROR: parser: parse error at or near "'"
This error occurs because, without a newline, PostgreSQL will assume that you are referring to two separate constants. If you wish to concatenate two string constants this way on a single line, PostgreSQL supports the || operator for text concatenation (see "Using Operators" in Chapter 4 for more details).
booktown=# SELECT 'book' || 'end' AS example; example --------- bookend (1 row)
Bit string constants provide a way to directly represent a binary value with an arbitrary sequence of ones and zeroes. Similarly to string constants, they are bound by single quotes, but they also must be preceded by a leading B character (which may be uppercase or lowercase). This character identifies to PostgreSQL that the forthcoming constant is a bit string, and not a normal string of character data.
Syntactically, the opening single quote must follow immediately after the leading B, and the bit string may not contain any character other than 0 or 1. While there cannot be whitespace within this string of bits, it can be continued across multiple lines just like regular string constants, as documented in the section called String constants.
Bit string constants are generally only useful when working with tables or functions that require binary values. Example 3-6 demonstrates the use of a bit string constant upon a simple table containing raw bytes. A bit string byte is inserted into a list of bytes in the my_bytes table, and insertion is verified with a simple query.
Far more frequently used than bit string constants are integer constants. PostgreSQL identifies an integer constant as any token which consists solely of a sequence of numbers (without a decimal point) and which is outside of single-quotes. Technically, SQL defines integer constants as a sequence of decimal digits with no decimal point. The range of values available for an integer constant depends largely on the context within which it is used, but PostgreSQL's default for the integer data type is a 4-byte signed integer, with range from -2147483648 to 2147483647.
Integer constants are used anywhere that you wish to represent a literal integer value. They are frequently used within mathematical operations, and in SQL commands which make reference to a column with an integer data type. Example 3-7 is a simple demonstration of the use of integer constants to update an author's numeric identifier via an UPDATE command.
Consider once again the authors table used in previous sections, which correlates a numeric author identifier with two character strings representing the author's first and last name. Suppose that, for administrative reasons, it has been deemed necessary that any author with an identifier of less than 100 must be modified to a value of more than 100.
The first step to correct this would be to locate any author with such an id value. An integer constant can first be used in a SELECT statement's WHERE clause to perform a less-than comparison to check.
Example 3-7. Using Integer Constants
booktown=# SELECT * FROM authors WHERE id < 100; id | last_name | first_name -------+-----------+------------------ 16 | Alcott | Louisa May (1 row) booktown=# SELECT * FROM authors WHERE id = 116; id | last_name | first_name -------+-----------+------------------ (0 rows) booktown=# UPDATE authors booktown-# SET id = 116 booktown-# WHERE id = 16; UPDATE 1 booktown=# SELECT * FROM authors WHERE id = 116; id | last_name | first_name -------+-----------+------------------ 116 | Alcott | Louisa May (1 row)
In Example 3-7, the WHERE clause in the SELECT statement compares the id column identifier against an integer constant of 100, returning one row. Once the author with the offending id is found, a second SELECT statement is issued to check for an existing author with an id of 116. This is to verify that the new id is not in use by another author within the authors table, as this column has been specified as requiring a unique identifier. Finally, an UPDATE statement is executed, again using integer constants in both the SET and WHERE clauses.
A floating-point constant is similar to an integer constant, but it is used to represent decimal values as well as whole integers. These are required whenever such a floating-point value must be represented literally within a SQL statement.
A floating-point constant can be represented in several forms, as shown in Table 3-4. Each occurrence of ## represents one or more digits.
Table 3-4. Floating Point Representations
In the first form, there must be at least one digit before or after the decimal point for PostgreSQL to recognize the value as a floating-point constant versus an integer constant. The other options involve having at least one digit before or after an exponent clause, denoted by the e in the list. The presence of either the decimal point, the exponent clause, or both, distinguishes an integer constant from a floating-point.
Each of these valid formats is represented in Example 3-8 through a simple SQL SELECT statement illustrating a variety of floating-point conventions.
Example 3-8. Valid Floating Point Values
booktown=# SELECT .04 AS small_float, booktown-# -16.63 AS negative_float, booktown-# 4e3 AS exponential_float, booktown-# 6.1e-2 AS negative_exponent; small_float | negative_float | exponential_float | negative_exponent -------------+----------------+-------------------+------------------- 0.04 | -16.63 | 4000 | 0.061 (1 row)
Boolean constants are much simpler than any other constant values recognized by PostgreSQL, as they may consist only of two possible values: true, and false. When PostgreSQL encounters either of these terms outside of single quotes, they are implicitly interpreted as Boolean constants, rather than a string constant. Example 3-9 shows this important distinction.
Example 3-9. The Difference Between true and 'true'
testdb=# SELECT true AS boolean_t, testdb-# 'true' AS string_t, testdb-# false AS boolean_f, testdb-# 'false' AS string_f; bool_t | string_t | bool_f | string_f --------+----------+--------+---------- t | true | f | false (1 row)
When the terms true or false are parsed by PostgreSQL outside of single quotes, they are implied Boolean values. As shown in Example 3-9, PostgreSQL displays values which are literally of the type boolean as t or f, though be careful not to try to use only t or f as Boolean constant values, as this will not be interpreted correctly by PostgreSQL, and will cause an error.
Special character symbols are characters with a pre-defined syntactic meaning in PostgreSQL. They are typically disallowed from being used in identifier names for this reason, though as mentioned in the section on quoted identifiers, this restriction can usually be worked around with quotes if need be.
Some special character symbols help to make up the "punctuation" of a SQL statement, much like parentheses, periods and commas do in the English language. Table 3-5 shows some common PostgreSQL-recognized syntactic symbols.
Table 3-5. Punctuation Symbols
Asterisk: used with the SELECT command to query all columns in the table, and used with the COUNT aggregate function to count all rows in a table.
Parentheses: these are used to group expressions, enforce operator precedence, and to make function calls. The use of parentheses is highly subjective to the context in which they are used.
Brackets: used in the selection of specific elements in an array, or in the declaration of an array type (e.g., with the CREATE TABLE command).
Semi-colons: used to terminate a SQL command. The only place it can be used within a statement is within a string constant, or within a quoted identifier.
Commas: Some commands use the comma to separate elements within a list.
Period: used in floating-point constants (e.g., 3.1415), as well as to reference column names as children of tables (e.g., table_name.column_name).
Colons: used to select slices from arrays.
Dollar sign: used in the body of a function definition to represent a positional parameter, or argument.
An operator is another type of special character symbol, and is used to perform an operation on identifiers or constants, returning a resultant value. Operators can be used for mathematical operations, such as addition, as well as to perform comparison and logical operations.
Consider again the books table, and its numeric author_id field. Recall that the author_id column is an integer used to identify an author. Now imagine that, due to a system modification, all author identifiers must be incremented by 1500. This can be achieved by evaluating the result of an operation (an operator expression) in an UPDATE statement upon the author_id column. This requires use of the addition (+) operator. An example of this can be seen in Example 3-10.
Example 3-10. Operators in Statements
booktown=# SELECT * FROM books; id | title | author_id | subject_id ------+-----------------------------+-----------+------------ 7808 | The Shining | 4156 | 9 156 | The Tell-Tale Heart | 15 | 9 4513 | Dune | 1866 | 15 4267 | 2001: A Space Odyssey | 2001 | 15 1608 | The Cat in the Hat | 1809 | 2 1590 | Bartholomew and the Oobleck | 1809 | 2 (6 rows) booktown=# UPDATE books SET author_id = author_id + 1500; UPDATE 6 booktown=# SELECT * FROM books; id | title | author_id | subject_id ------+-----------------------------+-----------+------------ 7808 | The Shining | 5656 | 9 156 | The Tell-Tale Heart | 1515 | 9 4513 | Dune | 3366 | 15 4267 | 2001: A Space Odyssey | 3501 | 15 1608 | The Cat in the Hat | 3309 | 2 1590 | Bartholomew and the Oobleck | 3309 | 2 (6 rows)
As you can see in Example 3-10, each author_id record is modified with the results of the + operator's operation upon the previous author_id value.
Common operators that you are may already familiar with include the basic mathematical operators: the + sign for the addition of two numeric values, the - sign for the subtraction of one numeric value from another, etc... Some of the more esoteric operators include the bitwise & and | operators, which modify binary values at the bit level.
In addition to these character symbol operators, it's important to acknowledge the existence of SQL keywords which are frequently called operators as well. Most notably, this includes the logical operators AND, OR, and NOT. While technically keywords, these terms are grouped with the operators because of their operational effect upon constants and identifiers.
Table 3-6 lists some fundamental PostgreSQL operators.
Table 3-6. Fundamental PostgreSQL Operators
Addition operator: adds two numeric types
Subtraction operator: subtracts one numeric type from another
Division operator: divides one numeric type by another
Multiplication operator: multiplies one numeric type by another
Factorial operator: returns an integer's factorial
Absolute value operator: returns the absolute value of a numeric value
Equivalence operator: compares two values for equivalence
Less-than comparison operator: evaluates whether or not one number is less than another
Greater-than operator: evaluates whether or not one number is larger than another
Regular expression operator: performs a regular expression comparison on text values
Logical AND operator: returns true if both Boolean conditions are true
Logical OR operator: returns true if at least one of two Boolean conditions is true
Logical NOT operator: returns the opposite of a Boolean condition
While many operators have different connotations depending on their context, the = operator is an especially important one due to its meaning when used with an UPDATE statement's SET clause.
While in most expressions the = operator is an equivalence operator (used to compare two values for equivalence), when following the SET clause and an identifier name in an UPDATE statement, the = is read as an assignment operator. This means that it is used to assign a new value to an existing identifier, as the SET term implies.
For more information on operators, see the section called Operators in Chapter 5.
Comments are blocks of text which, through special character sequences, can be used to embed non-SQL text within SQL code. These can be used within blocks of code, because PostgreSQL removes the commented areas from the input stream and treats it as whitespace. There are two styles of comments available: single-line comments, and multi-line comments.
Single-line comments are preceded by two dashes (--), and may either be on a line by themselves, or they may follow valid SQL tokens. (The comments themselves are not considered tokens to PostgreSQL's parser, as any character data following the -- sequence (up to the end of the line) is treated as whitespace.) This is demonstrated in Example 3-11.
Example 3-11. Single-line comments
testdb=# SELECT 'Test' -- This can follow valid SQL tokens, testdb-# -- or be on a line of it own. testdb-# AS example; example --------- Test (1 row)
Multi-line comments are begun with a sequential slash-asterisk (/*) sequence, and are terminated with a sequential asterisk-slash (*/) sequence. This style of commenting may already be familiar to C programmers, but there is one key difference between PostgreSQL's interpreter and the C language interpreter: PostgreSQL comments may be nested. Therefore, when you create a multi-line comment within another multi-line comment, the */ used to close the inner comment does not also close the outer comment. You can see a comment explanation in Example 3-12.
Example 3-12. Multi-line comments
testdb=# SELECT 'Multi' /* This comment extends across testdb*# * numerous lines, and can be testdb*# * /* nested safely */ */ testdb-# || '-test' AS example; example ------------ Multi-test (1 row)
Nesting comments can be useful if you have a file containing SQL syntax that you wish to comment a large portion of before sending to PostgreSQL for interpreting and execution. If you have already used multi-line comments within that document, and you wish to comment a large section which includes those comments, PostgreSQL is intelligent enough to recognize that a closing comment sequence (*/) only closes the most recently opened comment, and not the entire commented region.
The asterisk character by itself (without an adjacent slash character) has no special meaning within a comment. The extra asterisks in Example 3-12 on multi-line comments are provided only for aesthetic purposes and readability.
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-7 uses a simple SELECT statement to illustrate a basic, but complete, SQL statement and its components.
Table 3-7. A Simple SQL Query
id and name columns
As shown in the table, the SELECT statement contains the keywords SELECT and FROM. Together, the FROM keyword and states token compose what is known as a clause, as they modify and further describe 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 shows the output this query generates within the booktown database.
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-8 and Table 3-9 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-8. UPDATE Example: The SET Clause
new id value
Table 3-9. UPDATE Example: The WHERE Clause
string value to match
When executed, this statement examines each record's name column to find matches for the WHERE clause's stated condition (equivalence to the string constant 'Oregon'). Then, for each row 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 (specifies the updates to make), and WHERE (identifies the rows to update). 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 against 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 whenever the name column matches the value Oregon. It then checks the results of that UPDATE statement with another SELECT statement.