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