Hi,

I have find some data type conversion problems with Pgsql (or with me).

1) 	Query: select (1::float8 * 1::float8)::text;
	Result: Fri 31 Dec 22:00:01 1999

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.

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
Note: I think it's using daylight saving (02/13/1999), but for financial uses the result should be: 03/01/1999

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

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

Thanks,

Ricardo Coelho.


Thu, 11 Feb 1999 19:51:21 -0200
"Ricardo J.C.Coelho" <pulsar@truenet-ce.com.br>
Type conversion
> 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

Fri, 12 Feb 1999 03:10:02 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Type conversion