Request table schema details in PostgreSQL?

Question

I need to know the column type in PostgreSQL (i.e. varchar(20) ). I know that I could find this using \d something in psql, but I need this to be done with the selected query.
Is this possible in PostgreSQL?

Answer 1

You can fully describe the table using postgres with the following query:

 SELECT a.attname as Column, pg_catalog.format_type(a.atttypid, a.atttypmod) as Datatype FROM pg_catalog.pg_attribute a WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = ( SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^(TABLENAME)$' AND pg_catalog.pg_table_is_visible(c.oid) ) 

In this case, you will get the column names and data type.

You can also start the psql client using the -E option

 $ psql -E 

And then a simple \d mytable will print the queries used by postgres to describe the table. It works for every psql description.

Answer 2

In PostgreSQL, it is much easier to get a column type.

 SELECT pg_typeof(col)::text FROM tbl LIMIT 1 

The table must contain at least one row, of course. And you get the base type without type modifiers (if any). Use the alternative below if you need it too.
You can also use the function for constants. Manual on pg_typeof() .

For an empty (or any) table, you can use the pg_attribute system directory to get a complete list of columns and their corresponding type in order:

 SELECT attnum, attname AS column, format_type(atttypid, atttypmod) AS type FROM pg_attribute WHERE attrelid = 'myschema.mytbl'::regclass -- optionally schema-qualified AND NOT attisdropped AND attnum > 0 ORDER BY attnum; 

A guide to format_type() and types of object identifiers like regclass .

Answer 3

Yes, see information_schema .