The following bug has been logged on the website:
Bug reference: 8471
Logged by: Dan Rickner
Email address: dnrickner@taylor.edu
PostgreSQL version: 9.2.4
Operating system: CentOS
Description:
Our ERP stores student GPA values as a text string. I am trying to select
only valid gpa values (a number between 0 and 4.0). I have a function
called numeric that returns a bool if the value can be converted to a
number. When I run my query I get errors about values that should not be
considered in my outer where statement. The inner where is supposed to
filter out the bad data values:
-- function to return if a character string can be converted to a number
create or replace function isnumeric(varchar) returns boolean as $$
declare x numeric;
begin
x = $1::numeric;
return true;
exception when others then
return false;
end
$$
language plpgsql
immutable;
-- test table
create table tbl
(
id serial not null,
gpa varchar(6) null
);
-- insert bad data
insert into tbl (gpa) values ('A'), ('2.0'), ('12.5'), ('3.45'), (''),
('-'), ('-2.3'), ('-5');
select *
from
( -- this subquery returrns only numeric values
select a.id, trunc(a.gpa::numeric, 2) as gpa
from
(
select id, gpa, isnumeric(gpa) as num
from tbl
) as a
where a.num = true
) as b
-- filter the numeric values to the 4.0 range
where b.gpa between 0.0 and 4.0
