Re: converting number to string in query - Mailing list pgsql-novice

From Andreas Wenk
Subject Re: converting number to string in query
Date
Msg-id 4A535645.90406@netzmeister-st-pauli.de
Whole thread Raw
In response to converting number to string in query  (richard terry <rterry@pacific.net.au>)
List pgsql-novice
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

pgsql-novice by date:

Previous
From: richard terry
Date:
Subject: converting number to string in query
Next
From: Michael Gould
Date:
Subject: Table Partitioning