pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: Tony Capobianco <tcapobianco@prospectiv.com>
To: pgsql-sql@postgresql.org
Subject: Re: concatenate question
Date: Wed, 08 Dec 2010 09:08:33 -0500
Message-ID: <1291817313.1654.2.camel@tony1.localdomain> (raw )
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 >
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
view thread (11+ messages) latest in thread
Message-ID: <1291817313.1654.2.camel@tony1.localdomain>
Permalink: ../1291817313.1654.2.camel@tony1.localdomain/
Also on: postgresql.org/message-id/1291817313.1654.2.camel@tony1.localdomain
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tcapobianco@prospectiv.com
Subject: Re: concatenate question
In-Reply-To: <1291817313.1654.2.camel@tony1.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox