Re: converting number to string in query

richard terry schrieb:
> I couldn't find this in doc
>
> I want to do something like this:
> 'mystring' ||  9
>
> Thanks for any help.
>
> Richard
Hi,

without knowing which server you'r running and what the target is for that snippet, I have
some examples for you. Basically the concatenation is working.

this is PostgreSQL 8.4:

postgres=# SELECT 'mystring' || 9 as test;
    test
-----------
  mystring9
(1 row)

postgres=# SELECT 'mystring' || 9::char as test;
    test
-----------
  mystring9
(1 row)

But in between a statement by selecting a column of type text that's not working (anymore
since 8.3):

postgres=# CREATE TABLE test (id int, string text);
CREATE TABLE
postgres=# SELECT id FROM test WHERE string = 9;
ERROR:  operator does not exist: text = integer
LINE 1: SELECT id FROM test WHERE string = 9;
                                          ^
HINT:  No operator matches the given name and argument type(s). You might need to add
explicit type casts.
postgres=# SELECT id FROM test WHERE string = 9::char;
  id
----
(0 rows)

You have to typecast the integer 9:

postgres=# SELECT id FROM test WHERE string = 9::text;
  id
----
(0 rows)

Hope that helps.

Cheers

Andy