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 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. A simple example using booktown 's authors table might be updating an author's numeric identifier via another 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 it is a unique identifier, as it is a requirement of the authors table.

Finally, an UPDATE statement is executed, again using integer constants in both the SET and WHERE clauses.