CODEWITHSUNDEEP
postgresqlshellcommand-line
CodyBugstein
CodyBugstein

Reputation: 23322

Determine column data types in PostgreSQL

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.

Upvotes: 0

Views: 136

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78443

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;

Upvotes: 2

Related Questions