I use the psycopg2 module to work with Postgresql. There is such a thing

cursor.description

from which you can get only the OID of the column data type. Actually the question: How to get exactly the type name (integer, varchar, etc.)? Any function which returns the name on OID can eat? I looked at the documentation, but found nothing like that.

  • I only remember \d [имя_таблицы] . There was also some construction with SHOW CREATE TABLE but in MySQL (see serverfault.com/questions/231952/… ). - DimXenon
  • one
    @DimXenon is a psql chip, from the adapter for working with the database this is unlikely to work. - D-side
  • By topic: look for the pg_types table. Most likely, your answer is there. - D-side

1 answer 1

 import psycopg2 if __name__ == '__main__': DSN = 'host=HOST port=PORT dbname=DBNAME user=USER password=PASS' with psycopg2.connect(DSN) as connection: with connection.cursor() as cursor: cursor.execute("SELECT * FROM TABLE_NAME LIMIT 0") for i in cursor.description: cursor.execute("SELECT typname FROM pg_type WHERE oid={oid}".format(oid=i[1])) print("Column name: ", i[0], " Column type: ", cursor.fetchone()[0])

As indicated in the comments above, you need to search in the pg_type table. pg_type - service table in pg_catalog schema. https://www.postgresql.org/docs/current/static/catalogs.html It stores information about all data types. This table can be useful if you need to create your own data type in psycopg2. Details: http://initd.org/psycopg/docs/advanced.html#type-casting-of-sql-types-into-python-objects

Also, the name of the data type can be obtained differently (omitting the jumble of connections and cursors)

 SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'public' AND table_name = TABLE_NAME

The readable type name is in the data_type column. https://www.postgresql.org/docs/9.1/static/infoschema-columns.html The second solution has a plus: the data type is stored as varchar, whereas in pg_type the data type is name. Quote from the documentation:

It is not intended to use the general user.

  • Please try to write more detailed answers. I am sure the author of the question would be grateful for your expert commentary on the code above. - Nicolas Chabanovsky ♦