============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Terence Ingram
Your email address : [EMAIL PROTECTED]
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.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
