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.