Re: PQfmod and varchars

On 2005-04-22, Shachar Shemesh <psql@shemesh.biz> wrote:
> Hi list,
>
> I'm trying to find out, from a client, how many characters will fit in a 
> varchar field 
> Problem is that when I do "PQfmod" on a varchar field defined as 
> "varchar(20)", PQfmod returns "24".

Interpreting PQfmod requires a rather intimate knowledge of the internal
type implementations.

For several types, including varchar, the typmod is rather arbitrarily
the type's length limit plus the size of a varlena header (which appears
to be 4 bytes on all platforms, even 64-bit ones). This is arbitrary
because (thanks to multibyte characters) the value doesn't actually relate
to the storage size; presumably it once did in the past.

A quick summary of other types (accurate I believe as of 8.0):

bit:  typmod is the specified length exactly

bpchar (i.e. char(n)):  typmod is the length + VARHDRSZ

numeric: this is ugly, the typmod is ((prec << 16) | scale) + VARHDRSZ,
i.e. numeric(10,2) is ((10 << 16) | 2) + 4

interval: very complex due to attempts to support sql-standard intervals

time, timestamp: typmod is the specified precision exactly

varbit: typmod is the maximum bit length exactly

varchar: typmod is the maximum length + VARHDRSZ

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services