PostgreSQL - how do you get the column formats?

Question

I'm using PostgreSQL 9.3.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit.

I've done

psycopg2.connect

, got the cursor and can run lines of code similar to

cur.execute('SELECT latitude, longitude, date from db')
table = cur.fetchall()

From what I understand at http://initd.org/psycopg/docs/cursor.html, running

print(cur.description)

should show the type_code of each column. How come I don't get this?

I get

(Column(name='column_name', type_code=1043, display_size=None, internal_size=-1, precision=None, scale=None, null_ok=None), Column(name='data_type', type_code=1043, display_size=None, internal_size=-1, precision=None, scale=None, null_ok=None))

Another solution I've been suggested is running

cur.execute('select column_name, data_type from information_schema.columns')
cur.fetchall()
cols = cur.fetchall()

but this returns an empty list.

That's what I've tried. What do you suggest for getting the column formats?

Answer 1

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.