Hi,

I got the message "SQL state: 22P02"



to produce:

create table test (num varchar(20));

insert into test (num) values (null);
select * from test where cast(num as int8) = 0;   --ok, no error
insert into test (num) values ('123123');
select * from test where cast(num as int8) = 123123;   --ok, no error

insert into test (num) values ('                    ');
select * from test where cast(num as int8) = 123123;  -- error
select * from test where case when trim(num) <> '' then cast(num as int8) = 123123 else false end; -- ok, changed query and no error

insert into test (num) values ('a');
select * from test where cast(num as int8) = 123123;  -- error
select * from test where case when trim(num) <> '' then cast(num as int8) = 123123 else false end;  -- error

I need get all tuples was the table have converting the characters to number. If one conversion fail there not displayed.

Ex:
  num      return
  ----     ------
  0         0
  null      false
  12        12
  a         false
  12ab      false

it´s possible get these results ?


other question:

Why this message ("SQL state: 22P02") is not displayed with the SQL command on log in data/pg_log ?
only appears    

2007-11-22 12:55:13 BRT ERROR:  invalid input syntax for integer: "a"


thanks,

Franklin Haut