The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide by The PostgreSQL Global Development Group Paperback (6"x9"), 478 pages ISBN 9781906966065 RRP £14.95 ($19.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL 9.0 Reference Manual Volume 2 Buy the book here! >>> learn more

12.3.1 Data Type Mapping

Function arguments are converted from their PostgreSQL type to a corresponding Python type:

• PostgreSQL boolean is converted to Python bool.
• PostgreSQL smallint and int are converted to Python int. PostgreSQL bigint is converted to long in Python 2 and to int in Python 3.
• PostgreSQL real, double, and numeric are converted to Python float. Note that for the numeric this loses information and can lead to incorrect results. This might be fixed in a future release.
• PostgreSQL bytea is converted to Python str in Python 2 and to bytes in Python 3. In Python 2, the string should be treated as a byte sequence without any character encoding.
• All other data types, including the PostgreSQL character string types, are converted to a Python str. In Python 2, this string will be in the PostgreSQL server encoding; in Python 3, it will be a Unicode string like all strings.
• For nonscalar data types, see below.

Function return values are converted to the declared PostgreSQL return data type as follows:

• When the PostgreSQL return type is boolean, the return value will be evaluated for truth according to the Python rules. That is, 0 and empty string are false, but notably 'f' is true.
• When the PostgreSQL return type is bytea, the return value will be converted to a string (Python 2) or bytes (Python 3) using the respective Python builtins, with the result being converted bytea.
• For all other PostgreSQL return types, the returned Python value is converted to a string using the Python builtin str, and the result is passed to the input function of the PostgreSQL data type. Strings in Python 2 are required to be in the PostgreSQL server encoding when they are passed to PostgreSQL. Strings that are not valid in the current server encoding will raise an error, but not all encoding mismatches can be detected, so garbage data can still result when this is not done correctly. Unicode strings are converted to the correct encoding automatically, so it can be safer and more convenient to use those. In Python 3, all strings are Unicode strings.
• For nonscalar data types, see below.

Note that logical mismatches between the declared PostgreSQL return type and the Python data type of the actual return object are not flagged; the value will be converted in any case.

Tip: PL/Python functions cannot return either type RECORD or SETOF RECORD. A workaround is to write a PL/pgSQL function that creates a temporary table, have it call the PL/Python function to fill the table, and then have the PL/pgSQL function return the generic RECORD from the temporary table.