interesting difference for queries...

On Wed, 2002-12-04 at 11:21, Mario Weilguni wrote:

I noticed an interesting difference in query behaviour:

cms=# CREATE TABLE foo(bar int);
CREATE
cms=# SELECT * from foo where bar=1.7;

This is a numeric to integer coercion, which rounds

rbt=# select 1.7::int;
int4
------
2
(1 row)

bar
-----
(0 rows)

cms=# SELECT * from foo where bar='1.7';
ERROR: pg_atoi: error in "1.7": can't parse ".7"

This is a text to integer coercion, which doesn't round.

I guess the question is whether or not a numeric -> integer conversion
should 'wedge' numbers into the int, or throw an error.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Rod Taylor <rbt@rbt.ca> writes:

cms=# CREATE TABLE foo(bar int);
CREATE
cms=# SELECT * from foo where bar=1.7;

This is a numeric to integer coercion, which rounds

No, it's an integer to numeric promotion (the var is promoted, not the
constant). Obviously the '=' can never return true in this case.

cms=# SELECT * from foo where bar='1.7';
ERROR: pg_atoi: error in "1.7": can't parse ".7"

This is a text to integer coercion, which doesn't round.

And should not, IMHO. This is effectively the same as

... where bar = '1.7'::int

which it seems to me is quite correct to throw a bad-input error.

regards, tom lane

On Wed, 2002-12-04 at 12:22, Tom Lane wrote:

Rod Taylor <rbt@rbt.ca> writes:

cms=# CREATE TABLE foo(bar int);
CREATE
cms=# SELECT * from foo where bar=1.7;

This is a numeric to integer coercion, which rounds

No, it's an integer to numeric promotion (the var is promoted, not the
constant). Obviously the '=' can never return true in this case.

Ahh, sorry. I see I changed the query slightly when doing the verbose
explain.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc