From: | Edgardo Portal <egportal2002(at)yahoo(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: concatenate question |
Date: | 2010-12-09 16:16:48 |
Message-ID: | idqvdg$r0q$1@news.eternal-september.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 2010-12-08, Tony Capobianco <tcapobianco(at)prospectiv(dot)com> wrote:
> 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!
FWIW, I think you can also extend the built-in concatenate operator, though
I personally haven't used something like this in a production DB:
BEGIN TRANSACTION ;
CREATE FUNCTION concat_num_ttwotz(numeric, timestamp without time zone)
RETURNS text AS
'select $1::text || $2::text;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT ;
CREATE OPERATOR || (
PROCEDURE = concat_num_ttwotz
,LEFTARG = numeric
,RIGHTARG= timestamp without time zone
) ;
CREATE TABLE tony_test (
memberid numeric
,addeddate timestamp without time zone
) ;
INSERT INTO tony_test VALUES(1,CURRENT_DATE) ;
SELECT memberid || addeddate FROM tony_test ;
ROLLBACK ;
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitriy Igrishin | 2010-12-09 18:24:21 | Extended query protocol and exact types matches. |
Previous Message | Marcin Krawczyk | 2010-12-08 14:35:00 | Re: conditional aggregates |