information_schema.columns should provide you with the column data-type info.

For example, given this DDL:

create table foo
(
  id serial,
  name text,
  val int
);


insert into foo (name, val) values ('narf', 1), ('poit', 2);

And this query (filtering out the meta tables to get at your tables):

select *
from information_schema.columns
where table_schema NOT IN ('information_schema', 'pg_catalog')
order by table_schema, table_name;

Will yield 4 rows, for the table foo -- the three columns I defined, plus a FK.

SQL fiddle

Regarding psycopg2, the information_schema-related code that you have shown looks like it should work... What's the entirety of the code? I would also recommend trying to step through the code in a debugger (the built-in pdb is OK, but I would recommend pudb, as it's more full featured and easier to use, but still terminal-based. It only runs on *nix platforms, though, due to the underlying modules it uses.

Edit:

I was able to get the data_type info from information_schema using psycopg2 with the following code:

#!/usr/bin/env python

import psycopg2
import psycopg2.extras

conn = psycopg2.connect("host=<host> dbname=<dbname> user=<user> password=<password>")
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

cur.execute("""select *
               from information_schema.columns
               where table_schema NOT IN ('information_schema', 'pg_catalog')
               order by table_schema, table_name""")

for row in cur:
    print "schema: {schema}, table: {table}, column: {col}, type: {type}".format(
        schema = row['table_schema'], table = row['table_name'],
        col = row['column_name'], type = row['data_type'])

I prefer to use DictCursors, as I find them much easier to work with, but it should work with a regular cursor, too -- you would just need to change how you accessed the rows.

Also, regarding cur.description, that returns a tuple of tuples. If you want to get at the type_code there, you can do so like this:

print cur.description[0][1]

Where the first dimension in the index of the column you want to look at, and the second dimension is the datum within that column. type_code is always 1. So you could iterate over the outer tuple and always look at its second item, for example.

• character varying is a perfectly valid type:

$ psql

=# select pg_typeof(''::varchar);
     pg_typeof     
-------------------
 character varying

=# select 1::character varying(10);
 varchar 
---------
 1

however you can get other names (which might be the canonical one) from the pg_type table, using the oid hidden field.

=# select pg_typeof(''::varchar)::oid;
 pg_typeof 
-----------
      1043

=# select typname from pg_type where oid = pg_typeof(''::varchar)::oid;
 typname 
---------
 varchar

• you can customize the adapter used by psycog to represent your data: check the docs for adapting Python types to SQL syntax

def get_query_results_as_list_of_dicts(query):
        """
        runs a query and returns the result as a dict rather than a tuple
        :param query: SQL formatted string
        :return: list of dictionary objects. one for each result returned from the database
        """
        # Execute the query
        cur.execute(query)

        # Fetch all the rows as a list of dictionaries
        list_of_dicts = [dict(row) for row in cur.fetchall()]

        return list_of_dicts

From "Programming Python" by Mark Lutz:

curs.execute("Select * FROM people LIMIT 0")
colnames = [desc[0] for desc in curs.description]

As far as I understand, that's not possible without executing(running cur.execute())

But, If you want a Postgres solution using a function that can be used by Psycopg2 as a query, you may use this solution. As you were expecting, this will not execute your query, it simply creates a temporary View which allows us to query it's metadata using the catalog information_schema.columns

CREATE OR REPLACE function define_query(query text) 
  RETURNS TABLE( column_name text,data_type text)  
LANGUAGE plpgsql AS  
$$ 
DECLARE 
v_view_n TEXT := 'temp_view$'; 
BEGIN 
       EXECUTE format( 'CREATE OR REPLACE TEMP VIEW  %I AS %s', v_view_n,query);
       RETURN QUERY  select i.column_name::text, i.data_type ::text
           from information_schema.columns i where i.table_name = v_view_n;
END $$;

Once you've got this function, you can get the definition of any query by simply calling this function and not executing it.

knayak=# select * from define_query('select 1::int as a,''TWO''::text as b');
 column_name | data_type
-------------+-----------
 a           | integer
 b           | text
(2 rows)

Assuming you're using psycopg2 as your database driver, then the cursor.description field is what you want:

import pprint
import psycopg2
conn = psycopg2.connect('');
curs = conn.cursor()
curs.execute("SELECT 1 as col1, 2 as col2, 'text' as colblah");
pprint.pprint(curs.description)

produces:

(Column(name='col1', type_code=23, display_size=None, internal_size=4, precision=None, scale=None, null_ok=None),
 Column(name='col2', type_code=23, display_size=None, internal_size=4, precision=None, scale=None, null_ok=None),
 Column(name='colblah', type_code=705, display_size=None, internal_size=-2, precision=None, scale=None, null_ok=None))

The type codes are PostgreSQL's internal object IDs.

For more detail see the psycopg2 manual, which explains how to turn the type oids into type names, among other things.

The error message is not consistent with your code: you have brackets in you code ({cols}) that don't appear in the message.

You can use print(sql.as_string(conn)) to figure out what you are doing wrong.

Information schema vs. system catalogs

The information schema serves certain purposes. System catalogs are the actual source of truth in Postgres.

The information schema provides standardized views which help with portability - mostly across major Postgres versions as portability across different RDBMS platforms typically is an illusion once your queries are sophisticated enough to look up system catalogs. Notably, major RDBMS like Oracle, SQLite, IBM Db2, SAP ASE, or IBM Informix still don't support the information schema (as of June 2026).

Views in the information schema must jump through many hoops to achieve a format complying to the standard. This makes them slow, sometimes very slow. Compare plans and performance for these basic objects:

EXPLAIN ANALYZE SELECT * from information_schema.columns;
EXPLAIN ANALYZE SELECT * from pg_catalog.pg_attribute;

The difference is remarkable.

Your example

For your example SELECT * from tbl compare the two queries below for this simple table:

CREATE TEMP TABLE foo(
   a numeric(12,3)
 , b timestamp(0)
);

Using pg_attribute:

SELECT attname, format_type(atttypid, atttypmod) AS type
FROM   pg_attribute
WHERE  attrelid = 'foo'::regclass
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

format_type() returns the complete type with all modifiers:

attname | type
--------+-------------------------------
a       | numeric(12,3)
b       | timestamp(0) without time zone

Also note that the cast to regclass resolves the table name according to the current search_path. It raises an exception if the name is not valid. See:

• ERROR: could not find array type for datatype information_schema.sql_identifier

Using information_schema.columns:

SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'foo'
ORDER  BY ordinal_position;

The information is standardized, but incomplete:

column_name | data_type
------------+----------------------------
a           | numeric
b           | timestamp without time zone

To get full information for the data type you need to consider all of these columns additionally:

character_maximum_length
character_octet_length
numeric_precision
numeric_precision_radix
numeric_scale
datetime_precision
interval_type
interval_precision

Related answers:

• How to check if a table exists in a given schema
• List all columns for a specified table

List of pros & cons

The biggest pros (IMO) in bold.

Information schema views

• often simpler (depends)
• slow
• preprocessed, which may or may not suit your needs
• selective (users only see objects they have privileges for)
• conforming to an SQL standard (that's implemented by some of the major RDBMS)
• mostly portable across major Postgres versions
• do not require much specific knowledge about Postgres
• identifiers are descriptive, long and sometimes awkward

System catalogs

• often more complex (depends), closer to the source
• fast
• complete (system columns like oid included)
• not complying to an SQL standard
• less portable across major Postgres versions (but basics aren't going to change)
• require more specific knowledge about Postgres
• identifiers are terse, less descriptive but conveniently short

Arbitrary query

To get the same list of column names and types from a query, you could use a simple trick: CREATE a temporary table from the query output, then use the same techniques as above.

You can append LIMIT 0, since you do not need actual data:

CREATE TEMP TABLE tmp123 AS
SELECT 1::numeric, now()
LIMIT  0;

To get the data type of individual columns, you can also use the function pg_typeof():

SELECT pg_typeof(1);

The theory yes, though you could find it very complex indeed.

• Every table (select * from pg_class) has columns.
• Every column (select * from pg_attribute) optionally has a "typmod" number.
• For types with typmod (select * from pg_type) there will be a "typmodout" function.
• Running the typmod out function on a typmod number will return a string that can be concatenated with the type name to form the kind of user-readable signature you're used to (select 'numeric' || numerictypmodout(786441)) (select geography_typmod_out(1107460))

But, hey, psql generates the strings you want, if we look at what SQL it generates, maybe the answer is in there.

Sure enough, there is a magic function that takes a typeid and typmod and returns the magic string.

select a.attname, format_type(a.atttypid, a.atttypmod) from pg_attribute a where attname = 'geog';

With a join to pg_class you should be able to get this info per-table.

If you want the Python type classes, like you might get from a SQLALchemy column object, you'll need to build and maintain your own mapping. psycopg2 doesn't have one, even internally.

But if what you want is a way to get from an oid to a function that will convert raw values into Python instances, psycopg2.extensions.string_types is actually already what you need. It might look like it's just a mapping from oid to a name, but that's not quite true: its values aren't strings, they're instances of psycopg2._psycopg.type. Time to delve into a little code.

psycopg2 exposes an API for registering new type converters which we can use to trace back into the C code involved with typecasts; this centers around the typecastObject in typecast.c, which, unsurprisingly, maps to the psycopg2._psycopg.type we find in our old friend string_types. This object contains pointers to two functions, pcast (for Python casting function) and ccast (for C casting function), which would seem like what we want— just pick whichever one exists and call it, problem solved. Except they're not among the attributes exposed (name, which is just a label, and values, which is a list of oids). What the type does expose to Python is __call__, which, it turns out, just chooses between pcast and ccast for us. The documentation for this method is singularly unhelpful, but looking at the C code further shows that it takes two arguments: a string containing the raw value, and a cursor object.

>>> import psycopg2.extensions
>>> cur = something_that_gets_a_cursor_object()
>>> psycopg2.extensions.string_types[23]
<psycopg2._psycopg.type 'INTEGER' at 0xDEADBEEF>
>>> psycopg2.extensions.string_types23
100
>>> psycopg2.extensions.string_types23
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: invalid literal for int() with base 10: '10.0'
>>> string_types[1114]
<psycopg2._psycopg.type 'DATETIME' at 0xDEADBEEF>
>>> string_types1114
datetime.datetime(2018, 11, 15, 21, 35, 21)

The need for a cursor is unfortunate, and in fact, a cursor isn't always required:

>>> string_types23
100

But anything having to with converting actual string (varchar, for example) types is dependent on the PostgreSQL server's locale, at least in a Python 3 compilation, and passing None to those casters doesn't just fail— it segfaults. The method you mention, cursor.cast(oid, raw), is essentially a wrapper around the casters in psycopg2.extensions.string_types and may be more convenient in some instances.

The only workaround for needing a cursor and connection that I can think of would be to build essentially a mock connection object. If it exposed all of the relevant environment information without connecting to an actual database, it could be attached to a cursor object and used with string_typesoid or with cur.cast(oid, raw), but the mock would have be built in C and is left as an exercise to the reader.