Re: Selecting a constant question

"Larry McGhaw" <lmcghaw(at)connx(dot)com> writes:

> The statement above is contrary to my actual results. The proper length
> is returned in all non-const cases.
>
> Here is a specific example:
>
> test=# create table test1 ( a varchar(20), b char(10), c integer );
> CREATE TABLE

It's not returning a length at all though. It's returning the typmod, ie, the
thing in the parentheses above. In that respect it's perfectly correct to
return -1 for the '123' case as well since it's interpreted as an unbounded
string and has no maximum length. It happens to only be three characters but
then the values in the table could happen to be much less than the 10 or 20
characters you declared them as.

The reason you might want to get this has more to do with understanding the
semantics of the data you're receiving than optimizing storage. If you queried
a Numeric column you would get something very different from the length from
which you could extract the maximum precision and scale. This might help you
display or work with the results maintaining the precision and scale a user
expects.

One reason why it might be useful to add an actual measure of the expected
length (Postgres does make guesses about the length for planning purposes)
would be to so a driver could size buffers appropriately. For example, in psql
where we use cursors to process rows, we might want to automatically use a
fetch count calculated to be large enough to receive approximately one
ethernet frame of data.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com