Re: Getting list of supported types in Postgres

On Wed, Aug 14, 2013 at 02:17:26PM +0200, Ivan Radovanovic wrote:
> Hi guys,
>
> I am looking for a way to get list of supported types in Postgres
> together with information whether type can have optional size (like
> varchar for example), whether it can have precision (like decimal
> for example), and whether it can come as value of sequence (like all
> integer types for example), but I have trouble getting that info
> from pg_type table. This is SQL I was using:
>
> select
>     pg_catalog.format_type(oid, null),
>     *
> from
>     pg_type
> where
>     typnamespace = (select oid from pg_namespace where
> nspname='pg_catalog') and
>     typisdefined and
>     typname not like '$_%' escape '$' and
>     typname not like 'pg%' and
>     typtype = 'b'
> order by
>     typname
>
> apparently pg_catalog.format_type for some types return quoted name
> ("char" for example), also I can't find decimal in results (there is
> numeric, but I would like to have complete list of supported types,
> so decimal should be included too). In documentation it is said that
> typlen of -1 or -2 means that type is variable length, but I don't
> know how to find out if type can have additional precision?

Try psql -E, and run the \dT command to see the query it uses.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +