Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Constants

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:

  • String

  • Bit string

  • Integer

  • Floating point

  • Boolean

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.

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

Escape sequence

Description

\\

Literal backslash

\'

Literal apostrophe

\b

Backspace

\f

Form feed

\n

Newline

\r

Carriage return

\t

Tab

\ xxx

ASCII character with the corresponding octal number xxx

Warning

As a result of the backslashes' 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. Multiline 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 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.

Example 3-6. Using bit string constants

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.

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.

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.

Table 3-4. Floating-point representations

Representation

Example

##.##

6.4

## e[+-] ## ]

8e-8

[ ## ]. ## [e[+-] ## ]

.04e8

## .[ ## ][e[+-] ## ]

4.e5

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

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

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire