Numeric Datatype

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

Your name : Terence Ingram
Your email address : terence(at)socialchange(dot)net(dot)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.