Обсуждение: Numeric Datatype

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               : Terence Ingram
Your email address      : terence@socialchange.net.au


System Configuration
---------------------
  Architecture (example: Intel Pentium)         :

  Operating System (example: Linux 2.0.26 ELF)  : SunOS 5.8
Generic_108528-13 sun4u sparc SUNW,Ultra-4

  PostgreSQL version (example: PostgreSQL-7.0):   PostgreSQL-7.1

  Compiler used (example:  gcc 2.8.0)           :


Please enter a FULL description of your problem:
------------------------------------------------
I have created a database full of Agency details. The primary table being
agency. Each agency has an id called agency_uid. The datatype of the
agency_uid is numeric(20,0). An extract of the table schema:

           Attribute           |          Type           | Modifier
-------------------------------+-------------------------+----------
 agency_uid                 | numeric(20,0)               | not null
 ou_organization          | character varying(255)  | not null
 other_names               | character varying(255)  |
 .....
 .....

The problem occurs when I perform this query:
=> select * from agency where agency_uid = 1018929909863;
=> ERROR:  Unable to identify an operator '=' for types 'numeric' and
'float8'
        You will have to retype this query using an explicit cast

HOWEVER if I perform this query:
=> select * from agency where agency_uid = 200203210308178296;

I get a response and the relevant agency details are returned.

Some more background data. Currently in my agency table the agency_uid value
basically has either a length of 18 or 13 characters. The above example
testifies to that. The first query with agency_uid = 1018929909863 (13
characters long) fails while the query with agency_uid = 200203210308178296
(18 characters long) succeeds.

I became curious WHY one would succeed and the other generate an error. I
then tested various SELECT statements where the agency_uid had varying
lengths i.e.

select * from agency where agency_uid = 1
select * from agency where agency_uid = 12
select * from agency where agency_uid = 123
....
...
...
select * from agency where agency_uid = 123456789012345678

I discovered an interesting bug.

Basically where the agency_uid [numeric(20,0)] contains say a value with up
to 10 characters it works perfectly. It then fails and produces the error
(Unable to identify an operator '=' for types 'numeric' and 'float8' ...)
when the value contains 11 - 17 characters. Then suprisingly it starts
working again when the value has 18 or more characters in length. I didn't
bother to test past 20.

Why is this so?

Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

1) Create the following table:

CREATE TABLE "agency" (
        "agency_uid" numeric(20,0) NOT NULL,
        "ou_organization" character varying(255) NOT NULL,
        "other_names" character varying(255)
);

2) Populate it with some data:

Bear in mind the varying lengths of the agency_uid. So create 20 rows with
varying lengths of digits for the agency_uid. At least create one row with
each agency_uid equaling the length of:  1 - 10 characters, 11 - 17
characters, 18+ characters i.e.

insert into agency values ("12345", "blah blah", "blah  blah");
insert into agency values ("1234567890123", "blah blah", "blah  blah");
insert into agency values ("12345678901234567890", "blah blah", "blah
blah");

3) Run some simple select queries i.e.

select * from agency where agency_uid = 12345;
select * from agency where agency_uid = 1234567890123;
select * from agency where agency_uid = 12345678901234567890;

The findings should be the same as above.

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

I have found some work-arounds but no solution that fixes Postgres:

1)    When performing a select quote the value i.e. select * from agency
where agency_uid = '12345';

However not an option as we use the same code for accessing Sybase and
Postgres. Sybase doesn't like quoting of integers.

2)    I changed the datatype from numeric to bigint and that solved it.

However the solutions are really work arounds and I was hoping to keep the
numeric datatype.

"Terence Ingram" <terence@socialchange.net.au> writes:
> Why is this so?

Numeric literals too large to fit in an int are interpreted as floats.

You can work around the issue by explicitly coercing the literal to
numeric, or by single-quoting it (so that its type is not determined
until after agency_uid is known to be numeric), or by updating to PG
7.2 which uses a slightly different set of type-resolution rules.

            regards, tom lane

I said:
> You can work around the issue by explicitly coercing the literal to
> numeric, or by single-quoting it (so that its type is not determined
> until after agency_uid is known to be numeric), or by updating to PG
> 7.2 which uses a slightly different set of type-resolution rules.

Actually, you'll probably still need to do coercion in 7.2.  I was
fooled by a poorly chosen test case:

test72=# select * from z where n1 = 12345678901234567890;
 n1 | f1
----+----
(0 rows)

But:

test72=# select * from z where n1 = 1234567890123;
ERROR:  Unable to identify an operator '=' for types 'numeric' and 'double precision'
        You will have to retype this query using an explicit cast

What's happening here is that 7.2 defaults a number literal to be type
numeric if it has too many significant digits to be stored accurately
as a float.  But for values in between int and numeric, the default
interpretation is still float, and we still don't make the choice
between exact and inexact comparison for you.

            regards, tom lane