Re: pg_attribute.attlen -1 for varchar e pbchar fields

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/