Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #194 > unrolled thread

"explicit type casts" ?

Back to article view | Back to comp.databases.postgresql

Contents

  "explicit type casts" ? Bjarne Jensen <bjarne.b.jensen@gmail.com> - 2011-08-06 21:56 +0200
    Re: "explicit type casts" ? Robert Klemme <shortcutter@googlemail.com> - 2011-08-07 13:06 +0200
      Re: "explicit type casts" ? Bjarne Jensen <bjarne.b.jensen@gmail.com> - 2011-08-07 18:44 +0200
    Re: "explicit type casts" ? Mladen Gogala <gogala.mladen@gmail.com> - 2011-08-07 17:37 +0000
      Re: "explicit type casts" ? Bjarne Jensen <bjarne.b.jensen@gmail.com> - 2011-08-08 11:13 +0200
        Re: "explicit type casts" ? Mladen Gogala <gogala.mladen@gmail.com> - 2011-08-08 12:03 +0000

#194 — "explicit type casts" ?

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

[toc] | [next] | [standalone]

#195

On 06.08.2011 21:56, Bjarne Jensen wrote:
> 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?

Maybe you have trailing whitespace in the column value.

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

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/

[toc] | [prev] | [next] | [standalone]

#196

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

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

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

/Bjarne

[toc] | [prev] | [next] | [standalone]

#197

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

> 
> 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

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

[toc] | [prev] | [next] | [standalone]

#198

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

> mgogala=# select runway from procedures where runway::int=10;


So thats how it is used! Thanks a lot.

/ Bjarne

[toc] | [prev] | [next] | [standalone]

#199

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


> So thats how it is used! Thanks a lot.
> 
> / Bjarne

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web