How Do I Translate Postgresql Oid Using Python

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 ty

Solution 1:

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 hereAND    attnum >0ANDNOT attisdropped
ORDERBY attnum;

  attname   |          base_type          |         full_type
------------+-----------------------------+-----------------------------
 age_id     |integer|integer
 age        | text                        | text
 ageabk     |character|character(2)
 foo        |boolean|boolean
 log_up     |timestampwithouttime zone |timestampwithouttime zone

Note that format_type(..) displays the type including modifiers.