Data Types

The Simba PostgreSQL ODBC Connector supports many common data formats, converting between PostgreSQL data types and SQL data types.

The table below lists the supported data type mappings.

Note:

If the Use Unicode option (the UseUnicode key) is enabled, then the connector returns SQL_WCHAR instead of SQL_CHAR, and SQL_WVARCHAR instead of SQL_VARCHAR.

PostgreSQL Type SQL Type

Array

SQL_VARCHAR

BigInt

SQL_BIGINT

BigSerial

SQL_BIGINT

Bit

SQL_VARCHAR

  • If the length of the column is greater than the Max Varchar (MaxVarchar) setting, then SQL_LONGVARCHAR is returned instead.
  • If the Use Unicode option (the UseUnicode key) is enabled, then SQL_WVARCHAR is returned instead.
  • If the Use Unicode option (the UseUnicode key) is enabled and the column length is greater than the Max Varchar (MaxVarchar) setting, then SQL_WLONGVARCHAR is returned instead.

Bit Varying

SQL_VARCHAR

  • If the length of the column is greater than the Max Varchar (MaxVarchar) setting, then SQL_LONGVARCHAR is returned instead.
  • If the Use Unicode option (the UseUnicode key) is enabled, then SQL_WVARCHAR is returned instead.
  • If the Use Unicode option (the UseUnicode key) is enabled and the column length is greater than the Max Varchar (MaxVarchar) setting, then SQL_WLONGVARCHAR is returned instead.

Boolean

SQL_BIT

If the Show Boolean Column As String option (the BoolsAsChar key) is enabled, then SQL_VARCHAR is returned instead.

Box

SQL_VARCHAR

Bytea

(escape and hex formats)

SQL_VARBINARY

If the Bytea As LongVarBinary option (the ByteaAsLongVarBinary key) is enabled, then SQL_LONGVARBINARY is returned instead.

Char

SQL_CHAR

  • If the length of the column is greater than the Max Varchar (MaxVarchar) setting, then SQL_LONGVARCHAR is returned instead.
  • If the Use Unicode option (the UseUnicode key) is enabled, then SQL_WCHAR is returned instead.
  • If the Use Unicode option (the UseUnicode key) is enabled and the column length is greater than the Max Varchar (MaxVarchar) setting, then SQL_WLONGVARCHAR is returned instead.

Cid

SQL_VARCHAR

Cidr

SQL_VARCHAR

Circle

SQL_VARCHAR

Composite Types

SQL_VARCHAR

Date

SQL_TYPE_DATE

If you are using ODBC 2.0, the SQL type is SQL_DATE.

DateRange

SQL_VARCHAR

Decimal

SQL_NUMERIC

Double Precision

SQL_DOUBLE

Enum

SQL_VARCHAR

Float

(same as Double Precision)

SQL_DOUBLE

Geometry SQL_VARCHAR

Int4Range

SQL_VARCHAR

Int8Range

SQL_VARCHAR

Inet

SQL_VARCHAR

Integer

SQL_INTEGER

Interval

SQL_VARCHAR

Json

SQL_VARCHAR

JsonB

SQL_VARCHAR

Line

SQL_VARCHAR

LSeg

SQL_VARCHAR

MacAddr

SQL_VARCHAR

Money

SQL_DECIMAL

Numeric

SQL_NUMERIC

NumRange

SQL_VARCHAR

Oid

SQL_INTEGER

Path

SQL_VARCHAR

Pg_Lsn

SQL_VARCHAR

Point

SQL_VARCHAR

Polygon

SQL_VARCHAR

Real

SQL_REAL

Serial

SQL_INTEGER

SmallInt

SQL_SMALLINT

SmallSerial

SQL_SMALLINT

Text

SQL_VARCHAR

  • If the Use Unicode option (the UseUnicode key) is enabled, then SQL_WVARCHAR is returned instead.
  • If the Text As LongVarChar option (the TextAsLongVarchar key) is enabled, then SQL_LONGVARCHAR is returned instead.
  • If both options are enabled, then SQL_WLONGVARCHAR is returned instead.

Tid

SQL_VARCHAR

Time (with time zone)

SQL_TYPE_TIME

If you are using ODBC 2.0, the SQL type is SQL_TIME.

Time (without time zone)

SQL_TYPE_TIME

If you are using ODBC 2.0, the SQL type is SQL_TIME.

Timestamp

SQL_TYPE_TIMESTAMP

If you are using ODBC 2.0, the SQL type is SQL_TIMESTAMP.

TsQuery

SQL_VARCHAR

TsRange

SQL_VARCHAR

TstzRange

SQL_VARCHAR

TsVector

SQL_VARCHAR

Txid_Snapshot

SQL_VARCHAR

Uuid

SQL_GUID

VarChar

SQL_VARCHAR

  • If the length of the column is greater than the Max Varchar (MaxVarchar) setting, then SQL_LONGVARCHAR is returned instead.
  • If the Use Unicode option (the UseUnicode key) is enabled, then SQL_WVARCHAR is returned instead.
  • If the Use Unicode option (the UseUnicode key) is enabled and the column length is greater than the Max Varchar (MaxVarchar) setting, then SQL_WLONGVARCHAR is returned instead.

Xid

SQL_INTEGER