pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedImplicit/Explicit casting of the unknown type
3+ messages / 2 participants
[nested] [flat]
* Implicit/Explicit casting of the unknown type
@ 2001-03-26 20:03 Stephen Jackson <Stephen.Jackson@panting-deer.org.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Stephen Jackson @ 2001-03-26 20:03 UTC (permalink / raw)
To: pgsql-sql
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]
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Implicit/Explicit casting of the unknown type
@ 2001-04-03 23:45 Stephen Jackson <Stephen.Jackson@panting-deer.org.uk>
parent: Stephen Jackson <Stephen.Jackson@panting-deer.org.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Stephen Jackson @ 2001-04-03 23:45 UTC (permalink / raw)
To: pgsql-sql
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]
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Implicit/Explicit casting of the unknown type
@ 2001-04-04 03:34 Josh Berkus <josh@agliodbs.com>
parent: Stephen Jackson <Stephen.Jackson@panting-deer.org.uk>
0 siblings, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2001-04-04 03:34 UTC (permalink / raw)
To: Stephen Jackson <Stephen.Jackson@panting-deer.org.uk>; pgsql-sql
Steve,
Hmmm ... I'm using PostgreSQL for quite a few contortions of data, and
I can't say that I've encountered anything quite like the problems
you're having. It may be that you actually have a compile problem with
Postgres rather than a SQL problem.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-04-04 03:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-26 20:03 Implicit/Explicit casting of the unknown type Stephen Jackson <Stephen.Jackson@panting-deer.org.uk>
2001-04-03 23:45 ` Stephen Jackson <Stephen.Jackson@panting-deer.org.uk>
2001-04-04 03:34 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox