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