Constants v17
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:
digits digits.[digits][e[+-]digits] [digits].digits[e[+-]digits] digitse[+-]digits
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:
42 3.5 4. .001 5e2 1.925e-3
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:
CAST('string' AS type)
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:
CAST ( MULTISET ( < subquery > ) AS < datatype > )
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:
edb=# CREATE OR REPLACE TYPE project_table_t AS TABLE OF VARCHAR2(25); CREATE TYPE edb=# CREATE TABLE projects (person_id NUMBER(10), project_name VARCHAR2(20)); CREATE TABLE edb=# CREATE TABLE pers_short (person_id NUMBER(10), last_name VARCHAR2(25)); CREATE TABLE
edb=# INSERT INTO projects VALUES (1, 'Teach'); INSERT 0 1 edb=# INSERT INTO projects VALUES (1, 'Code'); INSERT 0 1 edb=# INSERT INTO projects VALUES (2, 'Code'); INSERT 0 1 edb=# INSERT INTO pers_short VALUES (1, 'Morgan'); INSERT 0 1 edb=# INSERT INTO pers_short VALUES (2, 'Kolk'); INSERT 0 1 edb=# INSERT INTO pers_short VALUES (3, 'Scott'); INSERT 0 1 edb=# COMMIT; COMMIT
edb=# SELECT e.last_name, CAST(MULTISET( edb(# SELECT p.project_name edb(# FROM projects p edb(# WHERE p.person_id = e.person_id edb(# ORDER BY p.project_name) AS project_table_t) edb-# FROM pers_short e;
last_name | project_table_t
-----------+-----------------
Morgan | {Code,Teach}
Kolk | {Code}
Scott | {}
(3 rows)