3.12. Data Type Declaration Syntax
This section documents the syntax of declaring data types.
Data type declaration commonly occurs in DDL statements, but also in CAST and EXECUTE BLOCK.
The syntax documented below is referenced from other parts of this language reference.
3.12.1. Scalar Data Types Syntax
The scalar data types are simple data types that hold a single value. For reasons of organisation, the syntax of BLOB types are defined separately in Section 3.12.2, “BLOB Data Types Syntax”.
Scalar Data Types Syntax
|
<domain_or_non_array_type> ::=
| <scalar_datatype>
| | <blob_datatype>
| | [TYPE OF] domain
| | TYPE OF COLUMN rel.col
|
|<scalar_datatype> ::=
| SMALLINT | INT[EGER] | BIGINT | INT128
| | REAL | FLOAT [(bin_prec)] | DOUBLE PRECISION
| | DECFLOAT [(dec_prec)]
| | BOOLEAN
| | DATE
| | TIME [{WITHOUT | WITH} TIME ZONE]
| | TIMESTAMP [{WITHOUT | WITH} TIME ZONE]
| | {DECIMAL | DEC | NUMERIC} [(precision [, scale])]
| | {VARCHAR | {CHAR | CHARACTER} VARYING} (length)
| [CHARACTER SET charset]
| | {CHAR | CHARACTER} [(length)] [CHARACTER SET charset]
| | {NCHAR | NATIONAL {CHARACTER | CHAR}} VARYING (length)
| | {NCHAR | NATIONAL {CHARACTER | CHAR}} [(length)]
| | BINARY [(length)]
| | {VARBINARY | BINARY VARYING} (length)
| Argument | Description |
|---|---|
domain | Domain (only non-array domains) |
rel | Name of a table or view |
col | Name of a column in a table or view (only columns of a non-array type) |
bin_prec | Binary precision, default is 24. 1 - 24: 32-bit single precision
25 - 53: 64-bit double precision (synonym of |
dec_prec | Decimal precision of |
precision | Numeric precision in decimal digits. From 1 to 38 |
scale | Scale, or number of decimals. From 0 to 38. It must be less than or equal to precision |
length | The maximum length of a string, in characters, or — for |
charset | Character set |
domain_or_non_array_type | Non-array types that can be used in PSQL code and casts |
3.12.1.1. Use of Domains in Declarations
A domain name can be specified as the type of a PSQL parameter or local variable. The parameter or variable will inherit all domain attributes. If a default value is specified for the parameter or variable, it overrides the default value specified in the domain definition.
If the TYPE OF clause is added before the domain name, only the data type of the domain is used: any of the other attributes of the domain — NOT NULL constraint, CHECK constraints, default value — are neither checked nor used.
However, if the domain is of a text type, its character set and collation are always used.
3.12.1.2. Use of Column Type in Declarations
Input and output parameters or local variables can also be declared using the data type of columns in existing tables and views.
The TYPE OF COLUMN clause is used for that, specifying relationname.columnname as its argument.
When TYPE OF COLUMN is used, the parameter or variable inherits only the data type and — for string types — the character set and collation.
The constraints and default value of the column are ignored.
3.12.2. BLOB Data Types Syntax
The BLOB data types hold binary, character or custom format data of unspecified size. For more information, see Section 3.7, “Binary Data Types”.
BLOB Data Types Syntax
|
<blob_datatype> ::=
| BLOB [SUB_TYPE {subtype_num | subtype_name}]
| [SEGMENT SIZE seglen] [CHARACTER SET charset]
| | BLOB [(seglen [, subtype_num])]
| | BLOB [(, subtype_num)]
| Argument | Description |
|---|---|
charset | Character set (ignored for subtypes other than |
subtype_num |
|
subtype_name |
|
seglen | Segment size, cannot be greater than 65,535, defaults to 80 when not specified. See also Segment Size |
If the SUB_TYPE and CHARACTER SET clauses are absent, then subtype BINARY (or 0) is used.
If the SUB_TYPE clause is absent and the CHARACTER SET clause is present, then subtype TEXT (or 1) is used.
3.12.3. Array Data Types Syntax
The array data types hold multiple scalar values in a single or multi-dimensional array. For more information, see Section 3.8, “Array Types”
Array Data Types Syntax
|
<array_datatype> ::=
| {SMALLINT | INT[EGER] | BIGINT | INT128} <array_dim>
| | {REAL | FLOAT [(bin_prec)] | DOUBLE PRECISION} <array_dim>
| | DECFLOAT [(dec_prec)] <array_dim>
| | BOOLEAN <array_dim>
| | DATE <array_dim>
| | TIME [{WITHOUT | WITH} TIME ZONE] <array_dim>
| | TIMESTAMP [{WITHOUT | WITH} TIME ZONE] <array_dim>
| | {DECIMAL | DEC | NUMERIC} [(precision [, scale])] <array_dim>
| | {VARCHAR | {CHAR | CHARACTER} VARYING} (length)
| <array_dim> [CHARACTER SET charset]
| | {CHAR | CHARACTER} [(length)] <array_dim>
| [CHARACTER SET charset]
| | {NCHAR | NATIONAL {CHARACTER | CHAR}} VARYING (length)
| <array_dim>
| | {NCHAR | NATIONAL {CHARACTER | CHAR}}
| [(length)] <array_dim>
| | BINARY [(length)] <array_dim>
| | {VARBINARY | BINARY VARYING} (length) <array_dim>
|
|<array_dim> ::= '[' [m:]n [,[m:]n ...] ']'
| Argument | Description |
|---|---|
array_dim | Array dimensions |
bin_prec | Binary precision, default is 24. 1 - 24: 32-bit single precision
25 - 53: 64-bit double precision (synonym of |
dec_prec | Decimal precision of |
precision | Numeric precision in decimal digits. From 1 to 38 |
scale | Scale, or number of decimals. From 0 to 38. It must be less than or equal to precision |
length | The maximum length of a string, in characters, or — for |
charset | Character set |
m, n | Integer numbers defining the index range of an array dimension |