Date: Mon, 27 Jun 2005 14:35:12 -0600
From: Michael Fuhr <mike@fuhr.org>
To: "jimmy.olsen" <jimmy.olsen@ig.com.br>
Cc: pgsql-sql@postgresql.org
Subject: Re: pg_attribute.attlen -1 for varchar e pbchar fields
Message-ID: <20050627203512.GA16166@winnie.fuhr.org>
References: <1119886449.29687.3.camel@dicaprio.akademie1.de>
	<000201c57b3d$c6e0f420$230a0a0a@ligaone.com.br>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <000201c57b3d$c6e0f420$230a0a0a@ligaone.com.br>

On Mon, Jun 27, 2005 at 02:27:41PM -0300, jimmy.olsen wrote:
>
> SELECT attname, atttypid, attlen
> FROM pg_attribute
> where atttypid IN(1042, 1043)
> 
> The attlen column always returns -1 for bpchar and varchar columns. the
> postgre version is 8.0.1. When I look the tables in pgAdmin the column
> lengths are correct. Where can I find the correct column lengths??

See the atttypmod column or the format_type() function:

CREATE TABLE foo (
    col_char_1      char(1),
    col_char_5      char(5),
    col_varchar_10  varchar(10),
    col_text        text
);

SELECT attname, attlen, atttypmod, format_type(atttypid, atttypmod)
FROM pg_attribute
WHERE attrelid = 'foo'::regclass AND attnum > 0;

    attname     | attlen | atttypmod |      format_type      
----------------+--------+-----------+-----------------------
 col_char_1     |     -1 |         5 | character(1)
 col_char_5     |     -1 |         9 | character(5)
 col_varchar_10 |     -1 |        14 | character varying(10)
 col_text       |     -1 |        -1 | text
(4 rows)

Notice that atttypmod is 4 greater than the declared length;
presumably the difference is due to the varlena header (the 32-bit
integer that stores the data length).  However, that's getting a
little close to internals, so you might want to stick with calling
format_type().

BTW, it's "PostgreSQL" or "Postgres," not "Postgre."

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/