### Get Postgres Tips and Tricks

Subscribe to get advanced Postgres how-tos.

2 The SQL Language : 2.1 SQL Syntax : 2.1.3 Constants

2.1.3 ConstantsThe kinds of implicitly-typed constants in Advanced Server are strings and numbers. Constants can also be specified with explicit types, which can enable more accurate representation and more efficient handling by the system. These alternatives are discussed in the following subsections.2.1.3.1 String ConstantsA 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 within a string constant, write two adjacent single quotes, e.g. 'Dianne''s horse'. Note that this is not the same as a double-quote character (").2.1.3.2 Numeric Constantsdigitse[+-]digitswhere digits is one or more decimal digits (0 through 9). At least one digit must be before or after the decimal point, if one is used. At least one digit must follow the exponent marker (e), if one is present. There may not be any spaces or other characters embedded in the constant. Note that any leading plus or minus sign is not actually considered part of the constant; it is an operator applied to the constant.A numeric constant that contains neither a decimal point nor an exponent is initially presumed to be type INTEGER if its value fits in type INTEGER (32 bits); otherwise it is presumed to be type BIGINT if its value fits in type BIGINT (64 bits); otherwise it is taken to be type NUMBER. Constants that contain decimal points and/or exponents are always initially presumed to be type NUMBER.2.1.3.3 Constants of Other TypesThe 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. The explicit type cast may be omitted if there is no ambiguity as to the type the constant must be (for example, when it is assigned directly to a table column), in which case it is automatically coerced.CAST can also be used to specify runtime type conversions of arbitrary expressions.MULTISET is an extension to CAST that converts subquery results into a nested table type. The synopsis is:CAST(MULTISET) is used to store a collection of data in a table.The following example demonstrates using MULTISET:

2 The SQL Language : 2.1 SQL Syntax : 2.1.3 Constants