| |
|
|
SQL Data Types
This chapter describes all of the SQL data types that PointBase supports. Data types define what type of data a column can contain. The following sections describe each PointBase data type in detail and discuss converting data types. Tables are provided at the end of the chapter to show the mappings between PointBase data types and industry standard and other common non-standard data types.
Data Types
PointBase supports the following data types for its column and parameter declarations.
- CHARACTER [(length)] or CHAR [(length)]
- VARCHAR (length)
- BOOLEAN
- SMALLINT
- INTEGER or INT
- DECIMAL [(p[,s])] or DEC [(p[,s])]
- NUMERIC [(p[,s])]
- REAL
- FLOAT(p)
- DOUBLE PRECISION
- DATE
- TIME
- TIMESTAMP
- CLOB [(length)] or CHARACTER LARGE OBJECT [(length)] or CHAR LARGE OBJECT [(length)]
- BLOB [(length)] or BINARY LARGE OBJECT [(length)]
CHARACTER [(length)] or CHAR [(length)]
The CHARACTER data type accepts character strings, including Unicode, of a fixed length. The length of the character string should be specified in the data type declaration; for example, CHARACTER(n) where n represents the desired length of the character string. If no length is specified during the declaration, the default length is 1.
The minimum length of the CHARACTER data type is 1 and it can have a maximum length up to the table page size. Character strings that are larger than the page size of the table can be stored as a Character Large Object (CLOB).
If you assign a value to a CHARACTER column containing fewer characters than the defined length, the remaining space is filled with blanks characters. Any comparisons made to a CHARACTER column must take these trailing spaces into account.
Attempting to assign a value containing more characters than the defined length results in the truncation of the character string to the defined length. If any of the truncated characters are not blank, an error is raised.
VARCHAR (length)
The VARCHAR data type accepts character strings, including Unicode, of a variable length is up to the maximum length specified in the data type declaration.
A VARCHAR declaration must include a positive integer in parentheses to define the maximum allowable character string length. For example, VARCHAR(n) can accept any length of character string up to n characters in length. The length parameter may take any value from 1 to the current table page size. Attempting to assign a value containing more characters than the defined maximum length results in the truncation of the character string to the defined length. If any of the truncated characters are not blank, an error is raised.
If you need to store character strings that are longer than the current table page size, the Character Large Object (CLOB) data type should be used.
BOOLEAN
The BOOLEAN data type supports the storage of two values: TRUE or FALSE. No parameters are required when declaring a BOOLEAN data type.
Use the case insensitive keywords TRUE or FALSE to assign a value to a BOOLEAN data type. Comparisons using the BOOLEAN data type should also use these keywords. If you attempt to assign any other value to a BOOLEAN data type, an error is raised.
SMALLINT
The SMALLINT data type accepts numeric values with an implied scale of zero. It stores any integer value between the range 2^ -15 and 2^15 -1. Attempting to assign values outside this range causes an error.
If you assign a numeric value with a precision and scale to a SMALLINT data type, the scale portion truncates, without rounding.
INTEGER or INT
The INTEGER data type accepts numeric values with an implied scale of zero. It stores any integer value between the range 2^ -31 and 2^31 -1. Attempting to assign values outside this range causes an error.
If you assign a numeric value with a precision and scale to an INTEGER data type, the scale portion truncates, without rounding.
NOTE: To store integer values beyond the range (2^-31) to (2^31)-1, use the DECIMAL data type with a scale of zero.
DECIMAL [(p[,s])] or DEC [(p[,s])]
The DECIMAL data type accepts numeric values, for which you may define a precision and a scale in the data type declaration. The precision is a positive integer that indicates the number of digits that the number will contain. The scale is a positive integer that indicates the number of these digits that will represent decimal places to the right of the decimal point. The scale for a DECIMAL cannot be larger than the precision.
DECIMAL data types can be declared in one of three different ways. The declaration of it controls how the number is presented to an SQL query, but not how it is stored.
- DECIMAL - Precision defaults to 38, Scale defaults to 0
- DECIMAL(p) - Scale defaults to 0
- DECIMAL(p, s) - Precision and Scale are defined by the user
In the above examples, p is an integer representing the precision and s is an integer representing the scale.
NOTE: If you exceed the number of digits expected to the left of the decimal point, an error is thrown. If you exceed the number of expected digits to the right of the decimal point, the extra digits are truncated.
NUMERIC [(p[,s])]
PointBase treats the NUMERIC data type in exactly the same way as the DECIMAL data type.
REAL
The REAL data type accepts approximate numeric values, up to a precision of 64. No parameters are required when declaring a REAL data type. If you attempt to assign a value with a precision greater than 64 an error is raised.
FLOAT(p)
The FLOAT data type accepts approximate numeric values, for which you may define a precision up to a maximum of 64. If no precision is specified during the declaration, the default precision is 64. Attempting to assign a value lager than the declared precision will cause an error to be raised.
DOUBLE PRECISION
The REAL data type accepts approximate numeric values, up to a precision of 64. No parameters are required when declaring a DOUBLE PRECISION data type. If you attempt to assign a value with a precision greater than 64 an error is raised.
DATE
The DATE data type accepts date values. No parameters are required when declaring a DATE data type. Date values should be specified in the form: YYYY-MM-DD. However, PointBase will also accept single digits entries for month and day values.
Month values must be between 1 and 12, day values should be between 1 and 31 depending on the month and year values should be between 0 and 9999.
Values assigned to the DATE data type should be enclosed in single quotes, preceded by the case insensitive keyword DATE; for example, DATE '1999-04-04'.
TIME
The TIME data type accepts time values. No parameters are required when declaring a TIME data type. Date values should be specified in the form: HH:MM:SS. An optional fractional value can be used to represent nanoseconds.
The minutes and seconds values must be two digits. Hour values should be between zero 0 and 23, minute values should be between 00 and 59 and second values should be between 00 and 61.999999.
Values assigned to the TIME data type should be enclosed in single quotes, preceded by the case insensitive keyword TIME; for example, TIME '07:30:00'.
TIMESTAMP
The TIMESTAMP data type accepts timestamp values, which are a combination of a DATE value and a TIME value. No parameters are required when declaring a TIMESTAMP data type. Timestamp values should be specified in the form: YYYY-MM-DD HH:MM:SS. There is a space separator between the date and time portions of the timestamp.
All specifications and restrictions noted for the DATE and TIME data types also apply to the TIMESTAMP data type.
Values assigned to the TIMESTAMP data type should be enclosed in single quotes, preceded by the case insensitive keyword TIMESTAMP; for example, TIMESTAMP '1999-04-04 07:30:00'.
CLOB [(length)] or CHARACTER LARGE OBJECT [(length)] or CHAR LARGE OBJECT [(length)]
The Character Large Object (CLOB) data type accepts character strings longer than those that are allowed in the CHARACTER [(length)] or VARCHAR (length) data types. The CLOB declaration uses the following syntax to specify the length of the CLOB in bytes:
In the above syntax, n is an unsigned integer that represents the length. K, M, and G correspond to Kilobytes, Megabytes or Gigabytes, respectively. If K, M, or G is specified in addition to n, then the actual length of n is the following:
The maximum size allowed for CLOB data types is 2 gigabytes. If a length is not specified, then a default length of one byte is used. CLOB values can vary in length from one byte up to the specified length.
BLOB [(length)] or BINARY LARGE OBJECT [(length)]
The Binary Large Object (BLOB) data type accepts binary values. The BLOB declaration uses the following syntax to specify the length in bytes:
In the above syntax, n is an unsigned integer that represents the length. K, M, and G correspond to Kilobytes, Megabytes or Gigabytes, respectively. If K, M, or G is specified in addition to n, then the actual length of n is the following:
The maximum size allowed for BLOB data types is 2 gigabytes. If a length is not specified, then a default length of one byte is used. BLOB values can vary in length from one byte up to the specified length.
Data Conversions and Assignments
The PointBase database allows two types of data conversions - implicit and explicit. An implicit data conversion is automatically performed between data types that are in the same data type family. Table 1 describes the data type families supported by PointBase. Implicit data conversions are performed as needed and are transparent to the user.
PointBase handles explicit data conversion using the SQL Scalar CAST function. This function converts a value from one PointBase data type to another in the same data type family.
PointBase also supports other non-SQL standard data types. Table 3 describes the mapping of non-SQL standard data types from other database vendors to PointBase data types.
| |
|
|