I am having bit of trouble with the psycopg2 Python module. I wrote a small code to extract some information from PostgreSQL table using psycopg2 module. I want to know the data type for each columns in the table:
import os, psycopg2, getpass, sys, string
userpass = getpass.getpass()
# Connect to the database
conn = psycopg2.connect("host=hostname dbname=db user=username password=%s" % (userpass))
cur = conn.cursor()
cur.execute("SELECT * FROM database.table;")
fieldTypes = [desc[1] for desc in cur.description]
print fieldTypes
When I execute this code, I get a list of integers (i.e. OIDs). Is there a way to convert this into something that's more understandable (e.g. 'str','int','bool' and so forth).
You can convert the "OID" to text by simply casting - provided the OID (Object Identifier) is actually a regtype (the OID-subtype for registered types) like you would get from the function pg_typeof().
Postgres will normally display values of the data type regtype as text to the user. Example:
SELECT pg_typeof('2013-1-1'::date);
pg_typeof
-----------
date
While internally it is an OID:
SELECT pg_typeof('2013-1-1'::date)::oid;
pg_typeof
-----------
1082
If your client does not do the same you can force it with an explicit cast:
SELECT pg_typeof('2013-1-1'::date)::text;
SELECT 1082::regtype::text;
Get types of all columns from system catalog
It's unclear how you actually retrieve the types. Consider this query to get full information:
SELECT attname
, atttypid::regtype AS base_type
, format_type(atttypid, atttypmod) AS full_type
FROM pg_catalog.pg_attribute
WHERE attrelid = 'public.tbl'::regclass -- your table name here
AND attnum > 0
AND NOT attisdropped
ORDER BY attnum;
attname | base_type | full_type
------------+-----------------------------+-----------------------------
age_id | integer | integer
age | text | text
ageabk | character | character(2)
foo | boolean | boolean
log_up | timestamp without time zone | timestamp without time zone
Note that format_type(..) displays the type including modifiers.
