Re: [HACKERS] Type conversion - Mailing list pgsql-hackers

From Thomas G. Lockhart
Subject Re: [HACKERS] Type conversion
Date
Msg-id 36C39B8A.4EB87D50@alumni.caltech.edu
Whole thread Raw
In response to Type conversion  ("Ricardo J.C.Coelho" <pulsar@truenet-ce.com.br>)
List pgsql-hackers
> I have some data type conversion problems with Pgsql
> 1)      Query: select (1::float8 * 1::float8)::text;
>         Result: Fri 31 Dec 22:00:01 1999

tgl=> select (1::float8 * 1::float8)::text;
Result:   1

Fixed in the current development sources. It requires a couple of new
routines in the system tables to work correctly, so has not been applied
to the v6.4.x tree. You can disable this incorrect coersion in v6.4.x
but it will not learn how to do it correctly without these extra
routines.

> 2)      Query: select (2000000::int8 * 2000000::int8)::text;
>         Result: ERROR: int8 conversion to int4 is out of range.
> Note: I need this conversion to use as argument of function.

Hmm. Needs a new routine in the system tables to avoid trying to convert
down to int4. Will look at it.

> 3)      Query: select '01/01/1999'::date + '1 month'::timespan;
>         Result OK: 02/01/1999 00:00:00
>         Query: select '01/01/1999'::date + '2 month'::timespan;
>         Result not OK: 02/28/1999 23:00:00

tgl=> select '01/01/1999'::date + '2 month'::timespan;
Result: Mon Mar 01 00:00:00 1999 PST

I think there was a problem in the date->datetime conversion wrt time
zone. Look on the web site in /pub/patches for some "dt.c" patches. It
is also fixed in the current and v6.4.x sources. If you aren't running
v6.4.2, it may be fixed there, and if you are running v6.4.2 then look
for the patches.

> 4)      Query: select sum(int4_field::int8)::text from table_a;
>         Result: Pgsql don't know how to transform node 107

tgl=> select sum(i::int8)::text from t1;
ERROR:  Function 'text(int8)' does not exist

But we already knew that from example (2), right?

> 5)      Table_a (query 4) has 400,000 rows. Is it normal postmaster 
> allocate 125MB of memory ?

Maybe. What query were you executing exactly? How many intermediate
values would be floating around? To help with speed, not all internal
allocations are freed before the end of the transaction.

Thanks for the report.
                        - Tom


pgsql-hackers by date:

Previous
From: Vadim Mikheev
Date:
Subject: Re: [HACKERS] TIME QUALIFICATION
Next
From: Bruce Momjian
Date:
Subject: Optimizer is fixed, and faster