Notes on Data Types

This section covers various behaviors and key information regarding the available data types.

CHAR, NCHAR, VARCHAR, NVARCHAR, LONGVARCHAR, and NLONGVARCHAR

•

CHAR and NCHAR columns are padded with blanks to “fill” the columns

•

The CHAR types store characters using the database code page, i.e., using one or more bytes per character. The NCHAR types store characters as UCS-2 two-byte values.

•

VARCHAR/NVARCHAR and LONGVARCHAR/NLONGVARCHAR 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>'

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.

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]

Limitations on LONGVARCHAR, NLONGVARCHAR and LONGVARBINARY

The following limitations apply to the LONGVARCHAR and LONGVARBINARY data types:

•

The LIKE predicate operates on the first 65500 bytes of the column data.

•

All other predicates operate on the first 256 bytes of the column data.

•

SELECT statements with GROUP BY, DISTINCT, and ORDER BY return all the data but only order on the first 256 bytes of the column data.

•

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 bytes. You can insert more than 15000 bytes by using a parameterized insert.

•

The maximum number of bytes returned in a single call by PSQL for a LONGVARCHAR, NLONGVARCHAR or LONGVARBINARY columns depends on the access method used by the application as discussed in the following table.

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 115

DATETIME Components and Valid Values

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 116

Operands Involving DATE, DATETIME, TIME, and TIMESTAMP

Operand 2 →

Operand 1 ↓

The CONVERT and CAST functions can be used with DATE, DATETIME, TIME, and TIMESTAMP as the following tables explain.

Table 117

Permitted CONVERT Operations

CONVERT From

Permitted Resultant Data Type (to)

DATE

DATE, DATETIME, TIMESTAMP, VARCHAR

DATETIME

Any of the supported CONVERT data types except for GUID, BINARY, and LONGVARBINARY. The type parameter for CONVERT requires a prefix of “SQL_.” See CONVERT (exp, type [, style ]).

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 118

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