3. Data Types and Subtypes
Data of various types are used to:
define columns in a database table in the
CREATE TABLE
statement or change columns usingALTER TABLE
declare or change a domain using the
CREATE DOMAIN
orALTER DOMAIN
statementsdeclare local variables in stored procedures, PSQL blocks and triggers and specify parameters in stored procedures
indirectly specify arguments and return values when declaring external functions (UDFs — user-defined functions)
provide arguments for the
CAST()
function when explicitly converting data from one type to another
Name | Size | Precision & Limits | Description |
---|---|---|---|
| 64 bits | From -263 to (263 - 1) | The data type is available in Dialect 3 only |
| Varying | The size of a | A data type of a dynamically variable size for storing large amounts of data, such as images, text, digital sounds. The basic structural unit is a segment. The blob subtype defines its content |
| n characters. Size in bytes depends on the encoding, the number of bytes in a character | from 1 to 32,767 bytes | A fixed-length character data type. When its data is displayed, trailing spaces are added to the string up to the specified length. Trailing spaces are not stored in the database but are restored to match the defined length when the column is displayed on the client side. Network traffic is reduced by not sending spaces over the LAN. If the number of characters is not specified, 1 is used by default. |
| 32 bits | From 0001-01-01 AD to 9999-12-31 AD |
|
| Varying (16, 32 or 64 bits) | precision = from 1 to 18, defines the least possible number of digits to store; scale = from 0 to 18, defines the number of digits after the decimal point | A number with a decimal point that has scale digits after the point. scale must be less than or equal to precision. Example: |
| 64 bits | 2.225 10-308 to 1.797 10308 | Double-precision IEEE, ~15 digits, reliable size depends on the platform |
| 32 bits | 1.175 10-38 to 3.402 1038 | Single-precision IEEE, ~7 digits |
| 32 bits | -2,147,483,648 up to 2,147,483,647 | Signed long |
| Varying (16, 32 or 64 bits) | precision = from 1 to 18, defines the exact number of digits to store; scale = from 0 to 18, defines the number of digits after the decimal point | A number with a decimal point that has scale digits after the point. scale must be less than or equal to precision. Example: |
| 16 bits | -32,768 to 32,767 | Signed short (word) |
| 32 bits | 0:00 to 23:59:59.9999 |
|
| 64 bits (2 X 32 bits) | From start of day 0001-01-01 AD to end of day 9999-12-31 AD | Date and time of day |
| n characters. Size in bytes depends on the encoding, the number of bytes in a character | from 1 to 32,765 bytes | Variable length string type. The total size of characters in bytes cannot be larger than (32KB-3), taking into account their encoding. The two trailing bytes store the declared length. There is no default size: the n argument is mandatory. Leading and trailing spaces are stored and they are not trimmed, except for those trailing characters that are past the declared length. |
Note About Dates Bear in mind that a time series consisting of dates in past centuries is processed without taking into account the actual historical facts, as though the Gregorian calendar were applicable throughout the entire series. |