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.
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:174005941952010-11-16 19:33:294005942842010-11-16 19:34:324005943492010-11-1619:35:224005943662010-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
I think the HINT is what you need to look at.<br /><br />Cast both columns to text.<br /><br /><div class="gmail_quote">OnTue, Dec 7, 2010 at 4:37 PM, Tony Capobianco <span dir="ltr"><<a href="mailto:tcapobianco@prospectiv.com">tcapobianco@prospectiv.com</a>></span>wrote:<br /><blockquote class="gmail_quote"style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">Here'smy table:<br /><br /> plsql_dw=# \d tmpsv_parent_master<br /> Table "staging.tmpsv_parent_master"<br/> Column | Type | Modifiers<br /> ----------------+-----------------------------+-----------<br/> memberid | numeric |<br/> addeddate | timestamp without time zone |<br /> sourceid | numeric |<br /> regcomplete | numeric(1,0) |<br /> optoutdate | date |<br /> bouncedate | date |<br /> websiteid | numeric |<br /> emailbounced | numeric(2,0) |<br /> emailok | numeric(2,0) |<br /> emailaddress | character varying(50) |<br /> srcwebsiteid | numeric |<br /> srcmemberid | numeric |<br /> sitetype | character varying |<br /> commissionpct | numeric |<br /> pricepermember | numeric |<br /> acceptrate | numeric(3,2) |<br /> mktgcenterid | numeric |<br /> label | character varying(32) |<br /><br /><br /> Why won't this work?<br/> plsql_dw=# select memberid || addeddate from tmpsv_parent_master<br /> limit<br /> 10;<br /> ERROR: operator does not exist: numeric || timestamp without<br /> time zone<br /> LINE 1: select memberid ||addeddate from tmpsv_parent_master<br /> limit ...<br /> ^<br /> HINT: No operatormatches the given name and argument type(s).<br /> You<br /> might need to add explicit type casts.<br /><br/> Thanks.<br /><font color="#888888"><br /><br /> --<br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br /></font></blockquote></div><br/><br clear="all" /><br />-- <br />Peter Steinheuser<br /><a href="mailto:psteinheuser@myyearbook.com">psteinheuser@myyearbook.com</a><br/>
Hey Tony,
Because there is no implicit conversion to text.
--
// Dmitriy.
Because there is no implicit conversion to text.
2010/12/8 Tony Capobianco <tcapobianco@prospectiv.com>
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
--
// Dmitriy.
What does a 'timestamp || numeric' actually look like? It means the timestamp written out a.k.a text and the same for numeric. The database does not know that that is what you wanted without the casts to text. Cheers, Ken On Tue, Dec 07, 2010 at 04:47:46PM -0500, Tony Capobianco 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 >
On Tue, Dec 7, 2010 at 1:47 PM, Tony Capobianco <tcapobianco@prospectiv.com> wrote: > Why did I need to cast both as text though? http://www.postgresql.org/docs/8.3/interactive/release-8-3.html#AEN87134 -- Regards, Richard Broersma Jr.
I don't know what Postgres version you're using but check out the doc related to String Functions and Operators.<br />Cheers,<br/> Peter<br /><br /><br /><div class="gmail_quote">On Tue, Dec 7, 2010 at 4:47 PM, Tony Capobianco <span dir="ltr"><<ahref="mailto:tcapobianco@prospectiv.com" target="_blank">tcapobianco@prospectiv.com</a>></span> wrote:<br/><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left:1ex;">Ok, that worked. Why did I need to cast both as text though?<br /><br /> plsql_dw=# select memberid::text||addeddate::textfrom<br /> tmpsv_parent_master limit 5;<br /> ?column?<br /> ------------------------------<br/> 4005941032010-11-16 19:32:17<br /> 4005941952010-11-16 19:33:29<br /> 4005942842010-11-1619:34:32<br /> 4005943492010-11-16 19:35:22<br /> 4005943662010-11-16 19:35:37<br /> (5 rows)<br /><br/> Thanks.<br /><br /> On Tue, 2010-12-07 at 16:43 -0500, Peter Steinheuser wrote:<br /> > I think the HINT is whatyou need to look at.<br /> ><br /> > Cast both columns to text.<br /> ><br /> > On Tue, Dec 7, 2010 at 4:37PM, Tony Capobianco<br /> > <<a href="mailto:tcapobianco@prospectiv.com" target="_blank">tcapobianco@prospectiv.com</a>>wrote:<br /> > Here's my table:<br /> ><br /> > plsql_dw=# \d tmpsv_parent_master<br /> > Table "staging.tmpsv_parent_master"<br />> Column | Type |<br /> > Modifiers<br /> ><br /> > ----------------+-----------------------------+-----------<br /> > memberid | numeric |<br /> > addeddate | timestamp without time zone |<br /> > sourceid | numeric |<br /> > regcomplete | numeric(1,0) |<br /> > optoutdate | date |<br /> > bouncedate | date |<br /> > websiteid | numeric |<br/> > emailbounced | numeric(2,0) |<br /> > emailok | numeric(2,0) |<br /> > emailaddress | character varying(50) |<br /> > srcwebsiteid | numeric |<br /> > srcmemberid | numeric |<br /> > sitetype | character varying |<br /> > commissionpct | numeric |<br /> > pricepermember | numeric |<br/> > acceptrate | numeric(3,2) |<br /> > mktgcenterid | numeric |<br /> > label | character varying(32) |<br /> ><br /> ><br/> > Why won't this work?<br /> > plsql_dw=# select memberid || addeddate from<br/> > tmpsv_parent_master<br /> > limit<br /> > 10;<br /> > ERROR: operator does not exist: numeric || timestamp<br /> > without<br /> > time zone<br /> > LINE 1: select memberid || addeddate from<br /> > tmpsv_parent_master<br /> > limit...<br /> > ^<br /> > HINT: No operator matches the givenname and argument<br /> > type(s).<br /> > You<br /> > might need to add explicittype casts.<br /> ><br /> > Thanks.<br /> ><br /> ><br /> > --<br /> > Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org" target="_blank">pgsql-sql@postgresql.org</a>)<br/> > To make changes to your subscription:<br /> > <a href="http://www.postgresql.org/mailpref/pgsql-sql" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/> ><br /> ><br /> ><br /> > --<br /> >Peter Steinheuser<br /> > <a href="mailto:psteinheuser@myyearbook.com" target="_blank">psteinheuser@myyearbook.com</a><br/><font color="#888888"><br /><br /><br /> --<br /> Sent via pgsql-sqlmailing list (<a href="mailto:pgsql-sql@postgresql.org" target="_blank">pgsql-sql@postgresql.org</a>)<br /> To makechanges to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br /><br clear="all" /><br/>-- <br />Peter Steinheuser<br /><a href="mailto:psteinheuser@myyearbook.com" target="_blank">psteinheuser@myyearbook.com</a><br/>
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_testName 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
On 2010-12-08, Tony Capobianco <tcapobianco@prospectiv.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;' LANGUAGESQL 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 ;