Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id E565A2E004A for ; Thu, 22 Nov 2007 11:24:47 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 47756-01 for ; Thu, 22 Nov 2007 11:24:36 -0400 (AST) Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.239]) by postgresql.org (Postfix) with ESMTP id 864AC2E004C for ; Thu, 22 Nov 2007 11:24:42 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id r21so981895wxc for ; Thu, 22 Nov 2007 07:24:41 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:subject:content-type:content-transfer-encoding; bh=7QoFJNbMlr6vyFZJ55CoXG5vU84hBnnfFRRTZSacmNY=; b=G/YMkMI5/RL7N4rVloKzyf1nsXNkyg3PuXshR1LEnmApQ1INPo1VWC1J02FzwV8hMqSN9jcSY/yokSMBnxR7xdx9tA7Sf49dV0hPABMmNRcNMaWD/iqnP0GPF1ScZY9Jcp74HkHQf/2uEhVsB6MlB9zfyHEFK+6xUl41PNUw6a8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:subject:content-type:content-transfer-encoding; b=is041vH/LZmnf/BTvEuydnEE+ncBB11tv26j4+wpVjGFyZnvGpeBHJsvvf1xBzEPC4WF0ESWrTe3N222Mr7reVnNijvC8y6dDS26PWaGzKWv0wfw08WNjk+wN0HQiaIByhMyYo7zClhNgx7d7vqaGUM2/vwOLTHtbsaixgCjs8o= Received: by 10.100.248.9 with SMTP id v9mr8572256anh.1195745081161; Thu, 22 Nov 2007 07:24:41 -0800 (PST) Received: from ?192.168.0.2? ( [201.34.189.230]) by mx.google.com with ESMTPS id d25sm1452769and.2007.11.22.07.24.37 (version=SSLv3 cipher=RC4-MD5); Thu, 22 Nov 2007 07:24:39 -0800 (PST) Message-ID: <4745AD44.1@gmail.com> Date: Thu, 22 Nov 2007 13:24:36 -0300 From: Franklin Haut User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: SQL state: 22P02 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/119 X-Sequence-Number: 29647 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