Constants

There are three implicitly typed constants for use in Postgres: strings, integers, and floating point numbers. Constants can also be specified with explicit types, which can enable more accurate representation and more efficient handling by the backend. The implicit constants are described below; explicit constants are discussed afterwards.

String Constants

Strings in SQL are arbitrary sequences of ASCII characters bounded by single quotes ("'", e.g. 'This is a string'). SQL92 allows single quotes to be embedded in strings by typing two adjacent single quotes (e.g. 'Dianne''s horse'). In Postgres single quotes may alternatively be escaped with a backslash ("\", e.g. 'Dianne\'s horse'). To include a backslash in a string constant, type two backslashes. Non-printing characters may also be embedded within strings by prepending them with a backslash (e.g. '\tab').

Integer Constants

Integer constants in SQL are collection of ASCII digits with no decimal point. Legal values range from -2147483648 to +2147483647. This will vary depending on the operating system and host machine.

Note that larger integers can be specified for int8 by using SQL92 string notation or Postgres type notation:

int8 '4000000000'  -- string style
'4000000000'::int8 -- Postgres (historical) style
     

Floating Point Constants

Floating point constants consist of an integer part, a decimal point, and a fraction part or scientific notation of the following format:

{dig}.{dig} [e [+-] {dig}]
     
where dig is one or more digits. You must include at least one dig after the period and after the [+-] if you use those options. An exponent with a missing mantissa has a mantissa of 1 inserted. There may be no extra characters embedded in the string.

Floating point constaints are of type float8. float4 can be specified explicitly by using SQL92 string notation or Postgres type notation:

float4 '1.23'  -- string style
'1.23'::float4 -- Postgres (historical) style
     

Constants of Postgres User-Defined Types

A constant of an arbitrary type can be entered using any one of the following notations:

type 'string'
'string'::type
CAST 'string' AS type
     
The value inside the string is passed to the input conversion routine for the type called type. The result is a constant of the indicated type. The explicit typecast may be omitted if there is no ambiguity as to the type the constant must be, in which case it is automatically coerced.

Array constants

Array constants are arrays of any Postgres type, including other arrays, string constants, etc. The general format of an array constant is the following:

{val1delimval2delim}
     
where delim is the delimiter for the type stored in the pg_type class. (For built-in types, this is the comma character (","). An example of an array constant is
{{1,2,3},{4,5,6},{7,8,9}}
     
This constant is a two-dimensional, 3 by 3 array consisting of three sub-arrays of integers.

Individual array elements can and should be placed between quotation marks whenever possible to avoid ambiguity problems with respect to leading white space.