Question & Answer
Question
What does "ERROR: pg_atoi: error in "
Answer
The error is the result of an implicit conversion being performed on a character field (varchar/char) while being compared to a numeric field or value.
For illustration of this issue, set up a table as follows:
1. create table a (f1 int, f2 varchar(2));
2. create table b (f1 int, f2 int);
3. insert into a values (1,'1');
4. insert into a values (2,'2');
5. insert into b values (1,'1');
6. insert into b values (2,'2');
7. insert into a values (2,'b');
Query 1
- select row_number() over (partition BY b.f2 ORDER by b.f2), a.f2 from a left outer join b on a.f2=b.f1 ;
This query is joining a varchar field to a integer field. The system will cast the varchar field to an integer thus resulting in "ERROR: pg_atoi: error in "b": can't parse "b" ."
Solution for Query 1
- select row_number() over (partition BY b.f2 ORDER by b.f2), a.f2 from a left outer join b on a.f2=cast(b.f1 as varchar(1)) ;
By casting the field b.f1 to be a varchar, the system will perform the join correctly and the select will complete successfully.
Query 2
- select * from a where f2=2;
This query is comparing an integer value to a varchar field (f2) and results in " ERROR: pg_atoi: error in "b": can't parse "b"."
Solution for Query 2
- select * from a where f2='2';
By placing single quotes around the integer value, the Plan Optimizer will treat all values between the single quotes as varchar.
[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Database","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Historical Number
NZ849684
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21574675