String 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 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:

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.

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 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.

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.

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 newline 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 Chapter 5, for more details on this operator).

booktown=# 
SELECT 'book' || 'end' AS example;

 example
---------
 bookend
(1 row)

Bit string constants

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.

testdb=# 
INSERT INTO my_bytes VALUES (B'00000000');

testdb=# 
SELECT my_byte FROM my_bytes;

 my_byte
----------
 10000000
 10000001
 10000101
 11111111
 00000000
(5 rows)

Integer constants

Integer constants are far more frequently used than bit string constants. PostgreSQL identifies an integer constant as any token that consists solely of a sequence of numbers (without a decimal point) and that 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 you wish to represent a literal integer value. They are used frequently within mathematical operations, as well as in SQL commands that reference 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.

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.

Floating-point constants

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.

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.

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

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.

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 and 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.