Received: from sand6.global.net.uk.noc.gxn.net (sand6.global.net.uk [195.147.246.105]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f33Nho804532 for ; Tue, 3 Apr 2001 19:43:56 -0400 (EDT) (envelope-from Stephen.Jackson@panting-deer.org.uk) Received: from pf6s01a03.client.global.net.uk ([195.147.161.247] helo=panting-deer.org.uk) by sand6.global.net.uk.noc.gxn.net with esmtp (Exim 3.03 #1) id 14ZMi2-0002kc-00 for pgsql-sql@postgresql.org; Sun, 04 Mar 2001 00:49:15 +0000 Message-ID: <3ACA60AA.A77E79F5@panting-deer.org.uk> Date: Wed, 04 Apr 2001 00:45:46 +0100 From: Stephen Jackson X-Mailer: Mozilla 4.7 [en] (Win95; I) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: Implicit/Explicit casting of the unknown type References: <3ABFA0AB.51025C8E@panting-deer.org.uk> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200104/30 X-Sequence-Number: 2369 Hi, Apologies for re-posting - this problem is giving me a bit of a headache. Basically, my problem is that in a number of (to me surprising) contexts an explicit cast from a string literal to some character data type is required by PostgreSQL, where such casts are not required by other rdbms with which I am more familiar. (Examples below.) Is there a rule of thumb I can use to tell when such a cast is required? I hope someone can help. Regards, Stephen Jackson Home: Stephen.Jackson@panting-deer.org.uk www.panting-deer.org.uk Work: Stephen.Jackson@looksystems.co.uk www.looksystems.co.uk On Mon, 26 Mar 2001 21:03:55 +0100, Stephen Jackson wrote: > > Hi, > > I am new to this list. I have browsed the list archives and tried a > search, but haven't found anything about my problem. > > I am porting several applications to PostgreSQL and have come across a > number of instances where a string literal does not work without an > explicit cast to some character based datatype. Two examples are given > below: one where the string literal is in the column list, and one where > it is in a WHERE clause. > > Are there bugs in the area of determining a type for the unknown type? > Or is explicit casting of string literals to a known type generally > required by PostgreSQL? > > TIA, > > Stephen Jackson > Home: Stephen.Jackson@panting-deer.org.uk www.panting-deer.org.uk > Work: Stephen.Jackson@looksystems.co.uk www.looksystems.co.uk > > [begin examples] > version > --------------------------------------------------------------------- > PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 > (1 row) > > DROP TABLE strange > DROP > CREATE TABLE strange ( record_id integer ) > CREATE > INSERT INTO strange ( record_id ) VALUES ( 10 ) > INSERT 5174249 1 > SELECT DISTINCT record_id, 123 > FROM strange WHERE record_id < 100 > record_id | ?column? > -----------+---------- > 10 | 123 > (1 row) > > SELECT DISTINCT record_id, 'Hello' > FROM strange WHERE record_id < 100 > ERROR: Unable to identify an ordering operator '<' for type 'unknown' > Use an explicit ordering operator or modify the query > SELECT record_id, 'Hello' > FROM strange WHERE record_id < 100 > record_id | ?column? > -----------+---------- > 10 | Hello > (1 row) > > SELECT COUNT(*) FROM strange > WHERE 'TR' || 'UE' = 'TRUE' > count > ------- > 1 > (1 row) > > SELECT COUNT(*) FROM strange > WHERE 'TRUE' = 'TRUE' > ERROR: Unable to identify an operator '=' for types 'unknown' and > 'unknown' > You will have to retype this query using an explicit cast > > [end examples] >