pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feed[Fwd: typlen field in the pg_type table]
2+ messages / 2 participants
[nested] [flat]
* [Fwd: typlen field in the pg_type table]
@ 2004-07-08 18:39 Paul Tilles <Paul.Tilles@noaa.gov>
0 siblings, 1 reply; 2+ messages in thread
From: Paul Tilles @ 2004-07-08 18:39 UTC (permalink / raw)
To: pgsql-interfaces
Forgot to mention that I am working with Version 7.4.3.
Paul Tilles wrote:
> For each table in my database, I am trying to extract the column name,
> column type and column length from the system catalog tables. I am able
> to get the column name from the pg_attribute table and the column type
> from the pg_type table. However, I am having a problem extracting the
> length of character columns from the typlen field of the pg_type table.
>
> For example, I have a column defined as a char(20). The column type is
> set to "bpchar" but the typlen value for this column is -1. The
> documentation says that a value of -1 indicates "... has a length
> word". Can I extract the length of the field (in this case 20) from any
> field in the system catalog tables?
>
> In the documentation (Section 43.29) for the typlen field, it says "...
> for a variable-length type, typlen is negative". In Section 10.4, Item
> 3 says "... fixed-length type(e.g., char ...". All of the char columns
> in my database have typlen = -1. IS A CHAR FIELD CONSIDERED A
> FIXED-LENGTH OR VARIABLE-LENGTH TYPE BY POSTGRESQL?
>
> Paul Tilles
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [Fwd: typlen field in the pg_type table]
@ 2004-07-08 18:58 Jim Buttafuoco <jim@contactbda.com>
parent: Paul Tilles <Paul.Tilles@noaa.gov>
0 siblings, 0 replies; 2+ messages in thread
From: Jim Buttafuoco @ 2004-07-08 18:58 UTC (permalink / raw)
To: Paul Tilles <Paul.Tilles@noaa.gov>; pgsql-interfaces
you want the atttypmod column which will be 4 bytes bigger that you specified in your create statement.
SELECT a.attnum,a.attname, t.typname || case when t.typname in ('bpchar','varchar') then '(' || atttypmod - 4 || ')'
else '' end
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = 'YOUR_TABLE_NAME_HERE'
AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid
ORDER BY a.attnum
---------- Original Message -----------
From: Paul Tilles <Paul.Tilles@noaa.gov>
To: interfaces <pgsql-interfaces@postgresql.org>
Sent: Thu, 08 Jul 2004 14:39:49 -0400
Subject: [INTERFACES] [Fwd: typlen field in the pg_type table]
> Forgot to mention that I am working with Version 7.4.3.
>
> Paul Tilles wrote:
>
> > For each table in my database, I am trying to extract the column name,
> > column type and column length from the system catalog tables. I am able
> > to get the column name from the pg_attribute table and the column type
> > from the pg_type table. However, I am having a problem extracting the
> > length of character columns from the typlen field of the pg_type table.
> >
> > For example, I have a column defined as a char(20). The column type is
> > set to "bpchar" but the typlen value for this column is -1. The
> > documentation says that a value of -1 indicates "... has a length
> > word". Can I extract the length of the field (in this case 20) from any
> > field in the system catalog tables?
> >
> > In the documentation (Section 43.29) for the typlen field, it says "...
> > for a variable-length type, typlen is negative". In Section 10.4, Item
> > 3 says "... fixed-length type(e.g., char ...". All of the char columns
> > in my database have typlen = -1. IS A CHAR FIELD CONSIDERED A
> > FIXED-LENGTH OR VARIABLE-LENGTH TYPE BY POSTGRESQL?
> >
> > Paul Tilles
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
------- End of Original Message -------
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-07-08 18:58 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-08 18:39 [Fwd: typlen field in the pg_type table] Paul Tilles <Paul.Tilles@noaa.gov>
2004-07-08 18:58 ` Jim Buttafuoco <jim@contactbda.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