Thread: Odd numeric->float4/8 casting behaviour
I noticed this odd discrepancy: postgres=# select -0.999::numeric(3,3)::float4 = -0.999::numeric(3,3);?column? ----------f (1 row) I believe this is happening because the numeric is being cast to float8 and then the float4-float8 cross-data-type operator is being used. It seems like it would be preferable to cast it to float4 and use the non-cross-data-type operator. They're both marked as implicit casts so I'm unclear what decides which gets used. Also, as a side note I was surprised to find the above being parsed as -(0.999::numeric(3,3)) rather than (-0.999)::numeric(3,3) is that expected? regression=# create or replace view x as select -0.999::numeric(3,3)::float4 = -0.999::numeric(3,3); CREATE VIEW regression=# \d x View "public.x" Column | Type | Modifiers ----------+---------+-----------?column? | boolean | View definition:SELECT (- 0.999::numeric(3,3)::real) = (- 0.999::numeric(3,3))::double precision; regression=# create or replace view x as select (-0.999)::numeric(3,3)::float4 = (-0.999)::numeric(3,3); CREATE VIEW regression=# \d x View "public.x" Column | Type | Modifiers ----------+---------+-----------?column? | boolean | View definition:SELECT -0.999::numeric(3,3)::real = -0.999::numeric(3,3)::double precision; -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Gregory Stark <stark@enterprisedb.com> writes: > I believe this is happening because the numeric is being cast to float8 and > then the float4-float8 cross-data-type operator is being used. It seems like > it would be preferable to cast it to float4 and use the non-cross-data-type > operator. They're both marked as implicit casts so I'm unclear what decides > which gets used. Without having traced through the code, I think the fact that float8 is a "preferred type" is driving it. It's not clear whether we could change this without getting into a "can't resolve ambiguous operator" problem. > Also, as a side note I was surprised to find the above being parsed as > -(0.999::numeric(3,3)) rather than (-0.999)::numeric(3,3) is that expected? Yeah, :: binds VERY tightly. regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > Gregory Stark <stark@enterprisedb.com> writes: >> I believe this is happening because the numeric is being cast to float8 and >> then the float4-float8 cross-data-type operator is being used. It seems like >> it would be preferable to cast it to float4 and use the non-cross-data-type >> operator. They're both marked as implicit casts so I'm unclear what decides >> which gets used. > > Without having traced through the code, I think the fact that float8 is > a "preferred type" is driving it. It's not clear whether we could > change this without getting into a "can't resolve ambiguous operator" > problem. This is pre-operator-families, I thought "preferred type" was new with them. Perhaps we should have preferred operators rather than preferred types? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Gregory Stark <stark@enterprisedb.com> writes: > This is pre-operator-families, I thought "preferred type" was new with them. No, preferred types have been around for a very long time. regards, tom lane