pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedhow can I get the length of columns of a table by system tables/views
4+ messages / 3 participants
[nested] [flat]
* how can I get the length of columns of a table by system tables/views
@ 2011-09-07 15:20 shuaixf <shuaixf@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: shuaixf @ 2011-09-07 15:20 UTC (permalink / raw)
To: pgsql-admin
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-tab...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: how can I get the length of columns of a table by system tables/views
@ 2011-09-09 07:41 Raghavendra <raghavendra.rao@enterprisedb.com>
parent: shuaixf <shuaixf@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Raghavendra @ 2011-09-09 07:41 UTC (permalink / raw)
To: shuaixf <shuaixf@gmail.com>; +Cc: pgsql-admin
>
> 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
Blog: http://raghavt.blogspot.com/
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-tab...
> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: how can I get the length of columns of a table by system tables/views
@ 2011-09-09 13:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Raghavendra <raghavendra.rao@enterprisedb.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2011-09-09 13:57 UTC (permalink / raw)
To: Raghavendra <raghavendra.rao@enterprisedb.com>; +Cc: shuaixf <shuaixf@gmail.com>; 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: how can I get the length of columns of a table by system tables/views
@ 2011-09-10 05:45 shuaixf <shuaixf@gmail.com>
parent: shuaixf <shuaixf@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: shuaixf @ 2011-09-10 05:45 UTC (permalink / raw)
To: pgsql-admin
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-tab...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-09-10 05:45 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-07 15:20 how can I get the length of columns of a table by system tables/views shuaixf <shuaixf@gmail.com>
2011-09-09 07:41 ` Raghavendra <raghavendra.rao@enterprisedb.com>
2011-09-09 13:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-09-10 05:45 ` shuaixf <shuaixf@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox