Type Mapping

The following table shows the mapping between Python types and PostgreSQL types, and vice versa.

If pg8000 doesn’t recognize a type that it receives from PostgreSQL, it will return it as a str type. This is how pg8000 handles PostgreSQL enum and XML types.

Python Type

PostgreSQL Type

Notes

bool

bool

int

int4

long

numeric

Python 2 only.

str

text (Python 3) bytea (Python 2)

unicode

text

Python 2 only.

float

float8

decimal.Decimal

numeric

pg8000.Bytea

bytea

Python 2 only.

bytes

bytea

Python 3 only.

datetime.datetime (wo/ tzinfo)

timestamp without time zone

datetime.datetime.max maps to infinity, and datetime.datetime.min maps to -infinity.

datetime.datetime (w/ tzinfo)

timestamp with time zone

datetime.datetime.max maps to infinity, and datetime.datetime.min maps to -infinity. The max and min datetimes have a UTC timezone.

datetime.date

date

datetime.date.max maps to infinity, and datetime.date.min maps to -infinity.

datetime.time

time without time zone

datetime.timedelta pg8000.Interval

interval

datetime.timedelta is used unless the interval has months, in which case pg8000.Interval is used

None

NULL

uuid.UUID

uuid

ipaddress.IPv4Address

inet

Python 3.3 onwards

ipaddress.IPv6Address

inet

Python 3.3 onwards

ipaddress.IPv4Network

inet

Python 3.3 onwards

ipaddress.IPv6Network

inet

Python 3.3 onwards

int

xid

list of int

INT4[]

list of float

FLOAT8[]

list of bool

BOOL[]

list of str

TEXT[]

list of unicode

TEXT[]

Python 2 only.

list of int

int2vector

Only from PostgreSQL to Python

JSON

json, jsonb

JSON string as an SQL parameter. Results returned as de-serialized JSON.