Notes on Data Types
This section covers various behaviors and key information regarding the available data types.
CHAR, VARCHAR, and LONGVARCHAR
•
CHAR columns are padded with blanks to “fill” the columns
•
VARCHAR/LONGVARCHAR columns are not padded with blanks to fill the columns. The significant data is terminated with a NULL character.
•
In all cases the trailing blanks are NOT significant in comparison operations (LIKE and =). However, in the LIKE case, if a space is explicitly entered in the query (like 'abc %'), the space before the wildcard does matter. In this example you are looking for 'abc<space><any other character>'
See also Limitations on LONGVARCHAR and LONGVARBINARY.
BINARY and LONGVARBINARY
•
BINARY columns are padded with binary zeros to fill the columns
•
LONGVARBINARY columns are NOT padded with blanks to fill the columns.
•
The database engine does not compare LONGVARBINARY columns. The database engine does compare fixed length BINARY data.
Pervasive PSQL now supports multiple LONGVARCHAR and LONGVARBINARY columns per table. The data is stored according to the offset in the variable length portion of the record. The variable length portion of data can vary from the column order of the data depending on how the data is manipulated. Consider the following example.
CREATE TABLE BlobDataTest
(
Nbr UINT, // Fixed record (Type 14)
Clob1 LONGVARCHAR, // Fixed record (Type 21)
Clob2 LONGVARCHAR, // Fixed record (Type 21)
Blob1 LONGVARBINARY, // Fixed record (Type 21)
)
On disk, the physical record would normally look like this:
[Fixed Data (Nbr, Clob1header, Clob2header, Blob1header)][ClobData1][ClobData2][BlobData1]
Now alter column Nbr to a LONGVARCHAR column:
ALTER TABLE BlobDataTest ALTER Nbr LONGVARCHAR
On disk, the physical record now looks like this:
[Fixed Data (Nbrheader, Clob1header, Clob2header, Blob1header)][ClobData1][ClobData2][BlobData1]
[NbrClobData]
As you can see, the variable length portion of the data is not in the column order for the existing data.
For newly inserted records, however, the variable length portion of the data is in the column order for the existing data. This assumes that all columns have data assigned (the columns are not NULL).
[Fixed Data (Nbrheader, Clob1header, Clob2header, Blob1header)][NbrClobData][ClobData1][ClobData2]
[BlobData1]
See also Limitations on LONGVARCHAR and LONGVARBINARY.
Limitations on LONGVARCHAR and LONGVARBINARY
The following limitations apply to the LONGVARCHAR and LONGVARBINARY data types:
•
The LIKE predicate operates on the first 65500 characters of the column data.
•
All other predicates operate on the first 256 characters of the column data.
•
SELECT statements with GROUP BY, DISTINCT, and ORDER BY return all the data but only order on the first 256 characters of the column data.
•
In a single call to SQLGetData, the maximum number of characters returned by Pervasive PSQL for a LONGVARCHAR or LONGVARBINARY columns is 65500. Multiple calls must be made to SQLGetData to retrieve column data over 65500 characters.
•
Though the maximum amount of data that can be inserted into a LONGVARCHAR/LONGVARBINARY column is 2GB, using a literal in an INSERT statement reduces this amount to 15000 characters. You can insert more than 15000 characters by using a parameterized insert.
DATETIME
The DATETIME data type represents a date and time value. The data type is stored internally as two 4-byte integers. The first four bytes store the number of days before or after the base date of January 1, 1900. The other four bytes store the time of day represented as the number of milliseconds after midnight.
The DATETIME data type can be indexed. The accuracy of DATETIME is one thousandth of a second.
DATETIME is a relational data type only. No corresponding transactional data type (Btrieve type) is available.
Format of DATETIME
The only permissible format for DATETIME is YYYY-MM-DD HH:MM:SS.mmm. (The CONVERT function contains an optional parameter that allows you to truncate the milliseconds portion of DATETIME. See the "Convert" function under Conversion Functions.)
The following table indicates the data components and their valid values for DATETIME.
Table 129
DATETIME Components and Valid Values
Component
Valid Values
YEAR (YYYY)
1753 to 9999
MONTH (MM)
01 to 12
DAY (DD)
01 to 31
HOUR (HH)
00 to 23
MINUTE (MM)
00 to 59
SECOND (SS)
00 to 59
MILLISECOND (mmm)
000 to 999
Compatibility of Data Types
The following table shows the resultant data type for addition and subtraction operations of DATE, TIME, TIMESTAMP, and DATETIME with other data types. Data types marked with an "X" are incompatible with DATE, TIME, TIMESTAMP, and DATETIME.
Table 130
Operands Involving DATE, DATETIME, TIME, and TIMESTAMP  
Operand 2 →
Operand 1 ↓
DATE
DATETIME
TIME
TIMESTAMP
BFLOAT4
* 
DATETIME
* 
TIMESTAMP
BFLOAT8
* 
DATETIME
* 
TIMESTAMP
BIGINT
* 
DATETIME
* 
* 
BINARY
* 
* 
* 
* 
BIT
* 
* 
* 
* 
CHAR
* 
* 
* 
* 
CURRENCY
* 
DATETIME
* 
* 
DATE
* 
* 
* 
* 
DATETIME
* 
* 
* 
* 
DECIMAL
* 
DATETIME
* 
* 
DOUBLE
* 
DATETIME
* 
TIMESTAMP
IDENTITY
* 
* 
* 
* 
INTEGER
DATE
DATETIME
* 
TIMESTAMP
LONGVARBINARY
* 
* 
* 
* 
LONGVARCHAR
* 
* 
* 
* 
MONEY
* 
DATETIME
* 
* 
NUMERIC
* 
DATETIME
* 
* 
NUMERICSA
* 
* 
* 
* 
NUMERICSLB
* 
* 
* 
* 
NUMERICSLS
* 
* 
* 
* 
NUMERICSTB
* 
* 
* 
* 
NUMERICSTS
* 
* 
* 
* 
REAL
* 
DATETIME
* 
TIMESTAMP
SMALLIDENTITY
DATE
DATETIME
* 
TIMESTAMP
SMALLINT
DATE
DATETIME
* 
TIMESTAMP
TIME
* 
* 
* 
* 
TIMESTAMP
* 
* 
* 
* 
TINYINT
DATE
DATETIME
* 
TIMESTAMP
UBIGINT
DATE
DATETIME
* 
TIMESTAMP
UINTEGER
DATE
DATETIME
* 
TIMESTAMP
UNIQUEIDENTIFIER
* 
* 
* 
* 
USMALLINT
DATE
DATETIME
* 
TIMESTAMP
UTINYINT
DATE
DATETIME
* 
TIMESTAMP
VARCHAR
* 
* 
* 
* 
The CONVERT and CAST functions can be used with DATE, DATETIME, TIME, and TIMESTAMP as the following tables explain.
Table 131
Permitted CONVERT Operations
CONVERT From
Permitted Resultant Data Type (to)
DATE
DATE, DATETIME, TIMESTAMP, VARCHAR
DATETIME
Any of the supported SQL_xxxx data types except for SQL_GUID, SQL_BINARY, and SQL_LONGVARBINARY
TIME
TIME, DATETIME, TIMESTAMP, VARCHAR
TIMESTAMP
DATE, DATETIME, TIME, TIMESTAMP, VARCHAR
VARCHAR
DATE, DATETIME, TIME, TIMESTAMP
*Note: The CONVERT function contains an optional parameter that allows you to truncate the milliseconds portion of DATETIME. See the "Convert" function under Conversion Functions.
 
Table 132
Permitted CAST Operations
CAST From
Permitted Resultant Data Type (to)
DATE
DATE, DATETIME, TIMESTAMP, VARCHAR
DATETIME
Any of the relational data types
TIME
TIME, DATETIME, TIMESTAMP, VARCHAR
TIMESTAMP
DATE, DATETIME, TIME, TIMESTAMP, VARCHAR
VARCHAR
DATE, DATETIME, TIME, TIMESTAMP
UNIQUEIDENTIFIER
The UNIQUEIDENTIFIER data type is a 16-byte binary value known as a globally unique identifier (GUID). A GUID is useful when a row must be unique among other rows.
UNIQUEIDENTIFIER requires a file format of 9.5 or greater .
You can initialize a column or local variable of UNIQUEIDENTIFIER the following ways:
•
By using the NEWID() scalar function. See NEWID ( ).
•
By providing a quoted string in the form 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' in which each x is a hexadecimal digit in the range 0-9 or A-F. For example, '1129619D-772C-AAAB-B221-00FF00FF0099' is a valid UNIQUEIDENTIFIER value.
If you provide a quoted string, all 32 digits are required. The database engine does not pad a partial string.
You may use only the following comparison operators with UNIQUEIDENTIFIER values:
Operator
Meaning
=
Equals
<> or !=
Not equal to
<
Less than
>
Greater than
<=
Less than or equal to
>=
Greater than or equal to
IS NULL
the value is NULL
IS NOT NULL
the value is not NULL
Note that ordering is not implemented by comparing the bit patterns of the two values.
Declaring Variables
You may declare variables of the UNIQUEIDENTIFIER data type and set the variable value with the SET statement.
DECLARE :Cust_ID UNIQUEIDENTIFIER DEFAULT NEWID()
DECLARE :ISO_ID uniqueidentifier
SET :ISO_ID = '1129619D-772C-AAAB-B221-00FF00FF0099'
Converting UNIQUEIDENTIFIER to Another Data Type
The UNIQUEIDENTIFER can be converted with the CAST or CONVERT scalar functions to any of the following data types:
•
CHAR
•
LONGVARCHAR
•
VARCHAR
For conversion examples, seeConversion Functions.
Representation of Infinity
When Pervasive PSQL is required by an application to represent infinity, it can do so in either a 4-byte (C float type) or 8-byte (C double type) form, and in either a hexadecimal or character representation, as shown in the following table:
Table 133
Infinity Representation
Value
Float Hexadecimal
Float Character
Double Hexadecimal
Double Character
Maximum Positive
 
 
0x7FEFFFFFFFFFFFFF
 
Maximum Negative
 
 
0xFFEFFFFFFFFFFFFF
 
Infinity Positive
0x7F800000
1E999
0x7FF0000000000000
1E999
Infinity Negative
0xFF800000
-1E999
0xFFF0000000000000
-1E999