Thread: Re: how can I get the length of columns of a table by system tables/views

SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (SELECT pg_class.oid                   FROM pg_class INNER JOIN pg_namespace
ON(pg_class.relnamespace = pg_namespace.oid                           AND lower(pg_namespace.nspname) = 'public')
          WHERE pg_class.relname='tablename') AND a.attnum > 0 AND NOT a.attisdropped
 
ORDER BY a.attnum;

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-can-I-get-the-length-of-columns-of-a-table-by-system-tables-views-tp4780987p4786248.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Why not to use information schema:

select column_name, character_maximum_length from information_schema.columns where table_name ='table name'


09.09.2011, 16:53, "shuaixf" <shuaixf@gmail.com>:
> SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod)
> FROM pg_catalog.pg_attribute a
> WHERE a.attrelid = (SELECT pg_class.oid
>                     FROM pg_class INNER JOIN pg_namespace
>                        ON (pg_class.relnamespace = pg_namespace.oid
>                            AND lower(pg_namespace.nspname) = 'public')
>                    WHERE pg_class.relname='tablename')
>   AND a.attnum > 0 AND NOT a.attisdropped
> ORDER BY a.attnum;
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/how-can-I-get-the-length-of-columns-of-a-table-by-system-tables-views-tp4780987p4786248.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql