Constants v16
The kinds of implicitly typed constants in EDB Postgres Advanced Server are strings and numbers. You can also specify constants with explicit types, which can enable more accurate representation and more efficient handling by the system.
String constants
A string constant in SQL is an arbitrary sequence of characters bounded by single quotes (')
, for example 'This is a string'
. To include a single-quote character in a string constant, include two adjacent single quotes, for example, 'Dianne''s horse'
. (The two single quotes diffe from a double-quote character (")
.)
Numeric constants
Numeric constants are accepted in these general forms:
Where digits
is one or more decimal digits (0 through 9). You must enter at least one digit before or after the decimal point, if one is used. At least one digit must follow the exponent marker (e)
, if one is present. Don't embed any spaces or other characters in the constant. Leading plus or minus signs aren't considered part of the constant. They are operators applied to the constant.
These are some examples of valid numeric constants:
A numeric constant that doesn't contain a decimal point or an exponent is initially presumed to be type INTEGER
if its value fits in type INTEGER
(32 bits). Otherwise it's presumed to be type BIGINT
if its value fits in type BIGINT
(64 bits). If its value then doesn't fit in type BIGINT
, it's taken to be type NUMBER
. Constants that contain decimal points or exponents are always initially presumed to be type NUMBER
.
The initially assigned data type of a numeric constant is just a starting point for the type resolution algorithms. In most cases the constant is automatically coerced to the most appropriate type depending on context. When necessary, you can force a numeric value to be interpreted as a specific data type by casting it as described in Constants of other types.
Constants of other types
CAST
You can enter a constant of an arbitrary type using the following notation:
The string constant’s text is passed to the input conversion routine for the type called type
. The result is a constant of the indicated type. You can omit the explicit type cast if there's no ambiguity as to the type of constant (for example, when it's assigned directly to a table column), in which case it's automatically coerced.
You can also use CAST
to specify runtime type conversions of arbitrary expressions.
CAST (MULTISET)
MULTISET
is an extension to CAST
that converts subquery results into a nested table type. The synopsis is:
Where subquery
is a query returning one or more rows, and datatype
is a nested table type.
CAST(MULTISET)
is used to store a collection of data in a table.
Example
This example uses MULTISET
: