Query the schema details of a table in PostgreSQL?

postgresql types dynamic-sql information-schema
David picture David · Dec 2, 2010 · Viewed 13.2k times · Source

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

Answer

Erwin Brandstetter picture Erwin Brandstetter · Oct 26, 2011

There is a much simpler way in PostgreSQL to get the type of a column.

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

The table must hold at least one row, of course. And you only get the base type without type modifiers (if any). Use the alternative below if you need that, too.
You can use the function for constants as well. The manual on pg_typeof().

For an empty (or any) table you can use query the system catalog pg_attribute to get the full list of columns and their respective 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;

The manual on format_type() and on object identifier types like regclass.

Related questions

Passing column names dynamically for a record variable in PostgreSQL

Using PostgreSQL, column values from a table for 1st record are stored in a record variable. for ex: let the variable be: recordvar recordvar.columnname gives the value of the column name specified. I will define the columname in a …

Read More
Difference between text and varchar (character varying)

What's the difference between the text data type and the character varying (varchar) data types? According to the documentation If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension. …

Read More
How to concatenate columns in a Postgres SELECT?

I have two string columns a and b in a table foo. select a, b from foo returns values a and b. However, concatenation of a and b does not work. I tried : select a || b from foo and select …

Read More