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.
testdb=# SELECT 'PostgreSQL''s great!' AS example; example --------------------- PostgreSQL's great! (1 row)
The parser will interpret the two adjacent single quotes within the string constant as a single, literal single quote. PostgreSQL will allow single quotes to be embedded by using a C-style backslash:
booktown=# SELECT 'PostgreSQL\'s C-style slashes are great!' AS example; example ----------------------------------------- PostgreSQL's C-style slashes are great! (1 row)
The C-style backslash escape sequences which PostgreSQL's parser supports 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 string constants to PostgreSQL that are separated by some amount of whitespace with at least one newline, and nothing else, then the strings are concatenated and viewed as if the string was 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)
As you can see, this syntax is equivalent to if you had entered SELECT 'bookend' AS example . At least one new-line is required for this interpretation to be possible, however, as spaces alone would result in the following error:
booktown=# SELECT 'book' 'end' AS mistake; ERROR: parser: parse error at or near "'"
This is 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 the section called Using Operators in Chapter 4 for more details).
booktown=# SELECT 'book' || 'end' AS example; example --------- bookend (1 row)