Thread: convert_numeric_to_scalar: unsupported type 354210

PostgreSQL 7.3.2:
Here is a piece of a table definition:
CREATE TABLE data
(
id SERIAL PRIMARY KEY,
description TEXT,
ra physreal,
dec physreal,
z physreal,
...);
physreal is the domain of numeric(20,14).

Then I created an index across ra,dec, and z (one index). I really 
haven't done anything else to the database. The table has ~290000 rows 
and I indexed after inserting the data. Strangely, I can only make 
weird selects using these fields. Look:
select dec from data limit 5;      dec
--------------- -1.2378252250 -1.2366515502 -1.2501212847 -1.0732052187 -1.1653486998
(5 rows)

select id from data where dec < 191.456; id
----  1  2
...this works.

select id from data where dec < 2.0;
ERROR:  convert_numeric_to_scalar: unsupported type 354210

This doesn't. The problem persists even when I create individual 
indices for each column. It seems this only occurs when I use a number 
that is "close" to a value in the column. What's going on and what does 
the error mean? Thanks.

><><><><><><><><><
AgentM
agentm@cmu.edu

AgentM <agentm@cmu.edu> writes:
> PostgreSQL 7.3.2:
> physreal is the domain of numeric(20,14).
> ERROR:  convert_numeric_to_scalar: unsupported type 354210

This is fixed in 7.3.3.
        regards, tom lane

> select id from data where dec < 2.0;
> ERROR:  convert_numeric_to_scalar: unsupported type 354210

Domains exposed (and introduced) a number of interesting issues in
regards to type switching for these things.

Cast the 2.0 value to the domain:CAST(2.0 AS physreal)

or quote it and let the system figure it out:dec < '2.0'


Simply put, without quotes the parser assumes that 2.0 is a numeric,
which doesn't have a direct < operator for use with the domain.

Another alternative would be to create a new < operator, but thats more
work than it's worth.

This has been fixed for 7.4 (the system implicitly coerces the domain to
numeric for the index comparison).
--
Rod Taylor <rbt@rbt.ca>

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