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?

Swordcraft

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.

Lazaro

The code in your edit works. Given this, and that I think I've posted the necessary parts of the code relating to using "description" I won't post code. Thanks a lot! – Swordcraft

Answer 2

select oid,typname from pg_type;

 oid  |   typname
------+-------------
   16 | bool
   23 | int4
   25 | text
 1043 | varchar
 1184 | timestamptz 

Cavern