Skip site navigation (1) Skip section navigation (2)

Re: [Fwd: typlen field in the pg_type table]

From: "Jim Buttafuoco" <jim(at)contactbda(dot)com>
To: Paul Tilles <Paul(dot)Tilles(at)noaa(dot)gov>,interfaces <pgsql-interfaces(at)postgresql(dot)org>
Subject: Re: [Fwd: typlen field in the pg_type table]
Date: 2004-07-08 18:58:54
Message-ID: 20040708185334.M89352@contactbda.com (view raw or flat)
Thread:
Lists: 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(dot)Tilles(at)noaa(dot)gov>
To: interfaces <pgsql-interfaces(at)postgresql(dot)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(at)postgresql(dot)org)
------- End of Original Message -------


In response to

pgsql-interfaces by date

Next:From: Tom LaneDate: 2004-07-10 00:07:56
Subject: Re: pgin.tcl pg_exec_prepared slow (was: Released...)
Previous:From: Paul TillesDate: 2004-07-08 18:39:49
Subject: [Fwd: typlen field in the pg_type table]

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group