Thread: how can I get the length of columns of a table by system tables/views
For example: CREATE TABLE tb(name varchar(32)); According to pg_class, pg_attribute, pg_type, I can get the tablename, column name, column type however, how to get the length of columns of a table by system tables/views? Thanks! -- 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-tp4778911p4778911.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
For example:
CREATE TABLE tb(name varchar(32));
You can get this with System functions.
select pg_column_size(name) from tb;
---
Regards,
Raghavendra
EnterpriseDB Corporation
On Wed, Sep 7, 2011 at 8:50 PM, shuaixf <shuaixf@gmail.com> wrote:
According to pg_class, pg_attribute, pg_type, I can get the tablename,
column name, column type
however, how to get the length of columns of a table by system tables/views?
Thanks!
--
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-tp4778911p4778911.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Raghavendra <raghavendra.rao@enterprisedb.com> writes: > On Wed, Sep 7, 2011 at 8:50 PM, shuaixf <shuaixf@gmail.com> wrote: >> According to pg_class, pg_attribute, pg_type, I can get the tablename, >> column name, column type >> however, how to get the length of columns of a table by system >> tables/views? > select pg_column_size(name) from tb; That would provide the physical size of individual values. I think what the OP was after was how to get the declared length limit of a column. That's encoded in pg_attribute.atttypmod. Rather than hard-wiring knowledge of the way it's encoded, it's best to rely on format_type(), which knows the rules: regression=# \d varchar_tbl Table "public.varchar_tbl" Column | Type | Modifiers --------+----------------------+----------- f1 | character varying(4) | regression=# select format_type(atttypid, atttypmod) from pg_attribute where attrelid = 'varchar_tbl'::regclass and attname= 'f1'; format_type ---------------------- character varying(4) (1 row) regards, tom lane
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-tp4778911p4788780.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.