[BioSQL-l] postgres 8.3 will not cast text to integer any longer

[Erik]

On Thu, March 20, 2008 23:49, Hilmar Lapp wrote:
> Hi Erik, thanks for the report. Given the error message,
> it looks
> more like the integer (which in reality is a string) can't
> be automatically converted to a string.

you are right, of course :)


Here is the postgres 8.3.1 result of your sql statements:

CREATE TABLE t1 (a varchar(10), b text, c integer);

SELECT * from t1 WHERE a = 1;   -- fails in 8.3.1
SELECT * from t1 WHERE b = 1;	  -- fails in 8.3.1
SELECT * from t1 WHERE c = '1'; -- ok

INSERT INTO t1 (a,b,c) VALUES ('a','b',1);

SELECT * from t1 WHERE a = 1;	  -- fails in 8.3.1
SELECT * from t1 WHERE b = 1;	  -- fails in 8.3.1
SELECT * from t1 WHERE c = '1'; -- ok

SELECT * from t1 WHERE a = 1::text;     -- ok
SELECT * from t1 WHERE b = 1::text;     -- ok
SELECT * from t1 WHERE c = integer '1'; -- ok

The failure is always (virtually) the same:
ERROR:  operator does not exist: character varying = integer
LINE 1: SELECT * from t1 WHERE a = 1;
                                 ^
HINT:  No operator matches the given name and argument
type(s). You might need to add explicit type casts.


Then there is the cast function: for instance, I can let
the test in t/16odba.t proceed faultlessly with

 $seq = $biodb->get_Seq_by_id( "cast(5456929 as text)" );


I am also doubtful/curious as to how this would affect the
various loading scripts which I was going to use - I want
to set up a GBrowse with human/mouse/flybase sequence
annotation to show ChipSeq data against.

But one thing at a time, I guess...


> So could you file this as a bug report on
> bugzilla.open-bio.org
> (category bioperl-db, this is actually not a BioSQL
> problem),

I'll make an entry in bugzilla/bioperl-db.


Thanks for you quick reply!


Erik Rijkers