Character Values

There are three character (or, as they are more commonly known, string) data types offered by PostgreSQL. A string value is just that ”a string of zero or more characters . The three string data types are CHARACTER( n ) , CHARACTER VARYING( n ) , and TEXT .

A value of type CHARACTER( n ) can hold a fixed-length string of n characters. If you store a value that is shorter than n , the value is padded with spaces to increase the length to exactly n characters. You can abbreviate CHARACTER( n ) to CHAR( n ) . If you omit the " ( n ) " when you create a CHARACTER column, the length is assumed to be 1.

The CHARACTER VARYING( n ) type defines a variable-length string of at most n characters. VARCHAR( n ) is a synonym for CHARACTER VARYING( n ) . If you omit the " ( n ) " when creating a CHARACTER VARYING column, you can store strings of any length in that column.

The last string type is TEXT . A TEXT column is equivalent to a VARCHAR column without a specified length ”a TEXT column can store strings of any length.

Syntax for Literal Values

A string value is a sequence of characters surrounded by single quotes. Each of the following is a valid string value:

 'I am a string' '3.14159265' '' 

The first example is obviously a string value. '3.14159265' is also a string value ”at first glance it may look like a numeric value but that fact it is surrounded by single quotes tells you that it is really a string. The third example ( '' ) is also a valid string: It is the string composed of zero characters (that is, it has a length of zero). It is important to understand that an empty string is not the same as a NULL value. An empty string means that you have a known value that just happens to be empty, whereas NULL implies that the value is unknown. Consider, for example, that you are storing an employee name in your database. You might create three columns to hold the complete name: first_name , middle_name , and last_name . If you find an employee whose middle_name is NULL , that should imply that the employee might have a middle name, but you don't know what it is. On the other hand, if you find an employee who has no middle name, you should store that middle_name as an empty string. Again, NULL implies that you don't have a piece of information; an empty string means that you do have the information, but it just happens to be empty.

If a string is delimited with single quotes, how do you represent a string that happens to include a single quote? There are three choices. First, you can embed a single quote within a string by entering two adjacent quotes. For example, the string "Where's my car?" could be entered as:

 'Where''s my car?' 

The other alternatives involve an escape character. An escape is a special character that tells PostgreSQL that the character (or characters) following the escape is to be interpreted as a directive instead of as a literal value. In PostgreSQL, the escape character is the backslash ( \) . When PostgreSQL sees a backslash in a string literal, it discards the backslash and interprets the following characters according to the following rules:

 \b is the backspace character \f is the form feed character \r is the carriage-return character \n is the newline character \t is the tab character \  xxx  (where  xxx  is an octal number) means the character whose ASCII value is  xxx  . 

If any character, other than those mentioned, follows the backslash, it is treated as its literal value. So, if you want to include a single quote in a string, you can escape the quote by preceding it with a backslash:

 'Where\'s my car?' 

Or you can embed a single quote (or any character) within a string by escaping its ASCII value (in octal), as in

 'Where7s my car?' 

To summarize, here are the three ways that you can embed a single quote within a string:

 'It''s right where you left it' 'It\'s right where you left it' 'It7s right where you left it' 

Supported Operators

PostgreSQL offers a large number of string operators. One of the most basic operations is string concatenation. The concatenation operator ( ) is used to combine two string values into a single TEXT value. For example, the expression

 'This is '  'one string' 

will evaluate to the value: 'This is one string' . And the expression

 'The current time is '  now() 

will evaluate to a TEXT value such as, ' The current time is 2002-01-01 19:45:17-04' .

PostgreSQL also gives you a variety of ways to compare string values. All comparison operators return a BOOLEAN value; the result will be TRUE , FALSE, or NULL . A comparison operator will evaluate to NULL if either of the operands are NULL .

The equality ( = ) and inequality ( <> ) operators behave the way you would expect ”two strings are equal if they contain the same characters (in the same positions ); otherwise , they are not equal. You can also determine whether one string is greater than or less than another (and of course, greater than or equal to and less than or equal to).

Table 2.1 ^[1] shows a few sample string comparisons.

^[1] You might find the format of this table a bit confusing at first. In the first column, I use the ' q ' character to represent any one of the operators listed in the remaining columns. So, the first row of the table tells you that 'string' < 'string' evaluates to FALSE , 'string' <= 'string' evaluates to TRUE , 'string' = 'string' evaluates to TRUE , and so forth. I'll use the ' q ' character throughout this chapter to indicate an operator.

Table 2.1. Sample String Comparisons

Operator ( q )
Expression	<	<=	=	<>	>=	>
'string' q 'string'	FALSE	TRUE	TRUE	FALSE	TRUE	FALSE
'string1' q 'string'	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE
'String1' q 'string'	TRUE	TRUE	FALSE	TRUE	FALSE	FALSE

You can also use pattern-matching operators with string values. PostgreSQL defines eight pattern-matching operators, but the names are a bit contrived and not particularly intuitive.

Table 2.2 contains a summary of the string operators.

The first set of pattern-matching operators is related to the LIKE keyword. ~~ is equivalent to LIKE. The ~~* operator is equivalent to ILIKE ”it is a case-insensitive version of LIKE . !~~ and !~~* are equivalent to NOT LIKE and NOT ILIKE , respectively.

The second set of pattern-matching operators is used to match a string value against a regular expression ( regular expression s are described in more detail in Chapter 1, "Introduction to PostgreSQL and SQL"). The naming convention for the regular expression operators is similar to that for the LIKE operators ” regular expression operators are indicated with a single tilde and LIKE operators use two tildes. The ~ operator compares a string against a regular expression (returning True if the string satisfies the regular expression). ~* compares a string against a regular expression, ignoring differences in case. The !~ operator returns False if the string value matches the regular expression (and returns True if the string satisfies the regular expression). The !~* operator returns False if the string value matches the regular expression, ignoring differences in case, and returns True otherwise.

Table 2.2. String Operators

Operator	Meaning	Case Sensitive?
	Concatenation	Not applicable
~	Matches regular expression	Yes
~~	Matches LIKE expression	Yes
~*	Matches regular expression	No
~~*	Matches LIKE expression	No
!~	Does not match regular expression	Yes
!~~	Does not match LIKE expression	Yes
!~*	Does not match regular expression	No
!~~*	Does not match LIKE expression	No

Type Conversion Operators

There are two important operators that you should know about before we go much further ”actually it's one operator, but you can write it two different ways.

The CAST() operator is used to convert a value from one data type to another. There are two ways to write the CAST() operator:

 CAST(expression AS type) expression::type 

No matter which way you write it, the expression is converted into the specified type . Of course, not every value can be converted into every type. For example, the expression CAST( 'abc' AS INTEGER ) results in an error ( specifically , ' pg_atoi: error in "abc": can't parse "abc" ') because ' abc ' obviously can't be converted into an integer.

Most often, your casting requirements will come in either of two forms: you will need to CAST() a string value into some other type, or you will need to convert between related types (for example, INTEGER into NUMERIC ). When you CAST() a string value into another data type, the string must be in the form required by the literal syntax for the target data type. Each of the following sections describes the literal syntax required by each type. When you convert between related data types, you may gain or lose precision. For example, when you convert from a fractional numeric type into an integer type, the value is rounded:

 movies=# SELECT CAST( CAST( 12345.67 AS FLOAT8 ) AS INTEGER );  ?column? ----------     12346