I have a table with one column as follows:

=# \d+ procedures
Table "public.procedures"
Column | Type | Modifiers | Storage | Description
-------------------+-------------------+-----------+----------+-------------
runway | character varying | | extended |


Now:

SELECT airport,ident,runway FROM procedures WHERE runway = 35;

ERROR: operator does not exist: character varying = integer
LINE 1: ...LECT airport,ident,runway FROM procedures WHERE runway = 35;
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.


I tried
WHERE runway = '35';
WHERE runway = "35";
WHERE runway LIKE '35';
WHERE runway LIKE '%35%';
WHERE runway LIKE '35%';

Last one works but why? and what is it all about?

How does one make a sure-fire workaround to this problem?

/Bjarne

On 06.08.2011 21:56, Bjarne Jensen wrote:
Maybe you have trailing whitespace in the column value.

How about a real solution: storing numeric values in numeric types?

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On 2011-08-07 13:06, Robert Klemme wrote:

Great idea, but I cannot control it - it is a vendor supplied database.
I'm just trying to work with it.

/Bjarne

On Sat, 06 Aug 2011 21:56:52 +0200, Bjarne Jensen wrote:

This may help:


[mgogala@medo ~]$ psql
psql (9.0.2)
Type "help" for help.

mgogala=# create temporary table procedures(runway varchar(10));
CREATE TABLE
mgogala=# insert into procedures values('10');
INSERT 0 1
mgogala=# select runway from procedures where runway=10;
ERROR: operator does not exist: character varying = integer
LINE 1: select runway from procedures where runway=10;
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
mgogala=# select runway from procedures where runway::int=10;
runway
--------
10
(1 row)



--
http://mgogala.byethost5.com

On 2011-08-07 19:37, Mladen Gogala wrote:


So thats how it is used! Thanks a lot.

/ Bjarne

On Mon, 08 Aug 2011 11:13:12 +0200, Bjarne Jensen wrote:


This actually rather sloppy coding practice. If the column cannot be
converted to integer, the query will fail. I would advise enveloping the
cast in your own function which would return something expected, instead
of throwing an error.



--
http://mgogala.byethost5.com