Subject: Re: concatenate question
From: Tony Capobianco <tcapobianco@prospectiv.com>
To: pgsql-sql@postgresql.org
In-Reply-To: <AANLkTin+GMRqqQqisMhnFCaqQi3xbv7SwBOQQ4RjHLpx@mail.gmail.com>
References: <1291757825.16970.18.camel@tony1.localdomain>
	<AANLkTi=cdwqU0DtFf68BuD=wb3tWfCToe4hn_AUWJFpQ@mail.gmail.com>
	<1291758466.16970.19.camel@tony1.localdomain>
	<AANLkTin+GMRqqQqisMhnFCaqQi3xbv7SwBOQQ4RjHLpx@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Date: Wed, 08 Dec 2010 09:08:33 -0500
Message-ID: <1291817313.1654.2.camel@tony1.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

Thanks so much to everyone for your responses.  You've been very
helpful.  I'm running PostGres 8.4 and we're migrating our datawarehouse
from Oracle 10.2.  I guess datatype is implicitly cast in oracle under
these circumstances:

SQL> create table tony_test as select memberid||addeddate "data" from
TMPSV_PARENT_MASTER where rownum < 5;

Table created.

SQL> desc tony_test
 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 data                                               VARCHAR2(59)

SQL> select * from tony_test;

data
-----------------------------------------------------------
3812482212010-06-23 13:53:38
3812510902010-06-23 14:12:25
3812622482010-06-23 15:24:45
3812725152010-06-23 16:35:24


Thanks!


On Tue, 2010-12-07 at 16:54 -0500, Peter Steinheuser wrote:
> I don't know what Postgres version you're using but check out the doc
> related to String Functions and Operators.
> Cheers,
>    Peter
> 
> 
> On Tue, Dec 7, 2010 at 4:47 PM, Tony Capobianco
> <tcapobianco@prospectiv.com> wrote:
>         Ok, that worked.  Why did I need to cast both as text though?
>         
>         plsql_dw=# select memberid::text||addeddate::text from
>         tmpsv_parent_master limit 5;
>                   ?column?
>         ------------------------------
>          4005941032010-11-16 19:32:17
>          4005941952010-11-16 19:33:29
>          4005942842010-11-16 19:34:32
>          4005943492010-11-16 19:35:22
>          4005943662010-11-16 19:35:37
>         (5 rows)
>         
>         Thanks.
>         
>         On Tue, 2010-12-07 at 16:43 -0500, Peter Steinheuser wrote:
>         > I think the HINT is what you need to look at.
>         >
>         > Cast both columns to text.
>         >
>         > On Tue, Dec 7, 2010 at 4:37 PM, Tony Capobianco
>         > <tcapobianco@prospectiv.com> wrote:
>         >         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.
>         >
>         >
>         >         --
>         >         Sent via pgsql-sql mailing list
>         (pgsql-sql@postgresql.org)
>         >         To make changes to your subscription:
>         >         http://www.postgresql.org/mailpref/pgsql-sql
>         >
>         >
>         >
>         > --
>         > Peter Steinheuser
>         > psteinheuser@myyearbook.com
>         
>         
>         
>         --
>         Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>         To make changes to your subscription:
>         http://www.postgresql.org/mailpref/pgsql-sql
> 
> 
> 
> -- 
> Peter Steinheuser
> psteinheuser@myyearbook.com