Determine column data types in PostgreSQL

Question

I'm interacting with a PostgreSQL on command-line based environment, and I'd like to be able to determine the data types of the table columns in this database.

For a simple example, when I request

SELECT * FROM products

I'd like to know if the product id column it returns is giving me text or integers.

Denis de Bernardy · Accepted Answer · 2013-12-08 14:05:21Z

2

You can get this kind of information by querying the catalog.

To find the specific queries, run psql -E (to echo hidden query) and then e.g. \d products. You'll see psql output various queries that yield information about your table, its column types, indexes, etc.

In this specific case, you'd run something like:

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 'products'::regclass AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

answered Dec 8, 2013 at 14:05

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CodyBugstein Over a year ago

Thanks! \d products worked for me, but I didn't understand the code you posted...

Collectives™ on Stack Overflow

Determine column data types in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related