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 | |
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. |