Data Types

Table 9-2 shows how the PostgreSQL data types are mapped to the standard ODBC data types. “Using the XML Data Type” describes PostgreSQL to Unicode data type mappings.

Table 9-2. PostgreSQL Data Types 

PostgreSQL	ODBC
Bigint	SQL_BIGINT
Bigserial	SQL_BIGINT
Bit 1	SQL_BIT
Bit varying	SQL_VARBINARY
Boolean	SQL_BIT
Bytea	SQL_VARBINARY
Character	SQL_CHAR
Character varying	SQL_VARCHAR
Date	SQL_TYPE_DATE
Double Precision	SQL_DOUBLE
Integer	SQL_INTEGER
Money	SQL_DOUBLE
Name	SQL_VARCHAR
Numeric 2	SQL_NUMERIC
Real	SQL_REAL
Serial	SQL_INTEGER
Smallint	SQL_SMALLINT
Text	SQL_LONGVARCHAR
Time 3	SQL_TYPE_TIME
Timestamp	SQL_TYPE_TIMESTAMP
Timestamp with timezone 4	SQL_VARCHAR
XML	SQL_WLONGVARCHAR

---

1

Bit maps to SQL_BIT when the length for the bit is 1. If the length is greater than 1, the driver maps the column to SQL_BINARY.

2

Numeric maps to SQL_NUMERIC if the precision of the Numeric is less than or equal to 38. If the precision is greater than 38, the driver maps the column to SQL_VARCHAR.

3

Time mapping changes based on the setting of the Fetch TWFS as Time option

4

Timestamp with timezone mapping changes based on the setting of the Fetch TSWTZ as Timestamp option.

See “Retrieving Data Type Information” for more information about data types.

Using the XML Data Type

By default, PostgreSQL returns XML data to the driver encoded as UTF-8. To avoid data loss, an application must bind XML data as SQL_C_WCHAR. The driver then returns the data as either UTF-8 or UTF-16, depending on platform and application settings. If the application binds XML data as SQL_C_CHAR, the driver converts it to the client character encoding, possibly causing data loss or corruption. To prevent any conversion of XML data, the application must set the option XML Describe Type to SQL_LONGVARBINARY (-4) and bind the data as SQL_C_BINARY.