Thread: concatenate question

Here's my table:

plsql_dw=# \d tmpsv_parent_master          Table "staging.tmpsv_parent_master"    Column     |            Type
  | Modifiers 
 
----------------+-----------------------------+-----------memberid       | numeric                     | addeddate
|timestamp without time zone | sourceid       | numeric                     | regcomplete    | numeric(1,0)
  | optoutdate     | date                        | bouncedate     | date                        | websiteid      |
numeric                    | emailbounced   | numeric(2,0)                | emailok        | numeric(2,0)
| emailaddress   | character varying(50)       | srcwebsiteid   | numeric                     | srcmemberid    |
numeric                    | sitetype       | character varying           | commissionpct  | numeric
| pricepermember | numeric                     | acceptrate     | numeric(3,2)                | mktgcenterid   |
numeric                    | label          | character varying(32)       | 
 


Why won't this work?
plsql_dw=# select memberid || addeddate from tmpsv_parent_master limit
10;
ERROR:  operator does not exist: numeric || timestamp without time zone
LINE 1: select memberid || addeddate from tmpsv_parent_master limit ...                       ^
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.

Thanks.

Tony Capobianco <tcapobianco@prospectiv.com> wrote:

> Here's my table:
> 
> plsql_dw=# \d tmpsv_parent_master
>            Table "staging.tmpsv_parent_master"
> 
> Why won't this work?
> plsql_dw=# select memberid || addeddate from tmpsv_parent_master limit
> 10;
> ERROR:  operator does not exist: numeric || timestamp without time zone
> LINE 1: select memberid || addeddate from tmpsv_parent_master limit ...
>                         ^
> HINT:  No operator matches the given name and argument type(s). You
> might need to add explicit type casts.

You can read? ;-)

You have to CAST the INT and the TIMESTAMP both into a STRING-typ, for
instance:

test=*# select 1::int || now()::timestamp;
ERROR:  operator does not exist: integer || timestamp without time zone
ZEILE 1: select 1::int || now()::timestamp;                      ^
TIP:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
test=!# rollback;
ROLLBACK
Zeit: 0,124 ms
test=# select 1::text || now()::text;           ?column?
--------------------------------12010-12-11 14:42:12.571931+01
(1 Zeile)



Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°