Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id CD86E1337B4F for ; Tue, 7 Dec 2010 17:52:28 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 93759-03 for ; Tue, 7 Dec 2010 21:52:21 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mh7.mail.rice.edu (mh7.mail.rice.edu [128.42.199.46]) by mail.postgresql.org (Postfix) with ESMTP id 3F424133655C for ; Tue, 7 Dec 2010 17:52:21 -0400 (AST) Received: from mh7.mail.rice.edu (localhost.localdomain [127.0.0.1]) by mh7.mail.rice.edu (Postfix) with ESMTP id EE16C28F82E; Tue, 7 Dec 2010 15:52:20 -0600 (CST) X-Virus-Scanned: by amavis-2.6.4 at mh7.mail.rice.edu, auth channel Received: from mh7.mail.rice.edu ([127.0.0.1]) by mh7.mail.rice.edu (mh7.mail.rice.edu [127.0.0.1]) (amavis, port 10026) with ESMTP id 4Pd+g+NA0nXW; Tue, 7 Dec 2010 15:52:20 -0600 (CST) X-SMTP-Auth: no X-SMTP-Auth: no Received: from aart.is.rice.edu (aart.is.rice.edu [168.7.56.48]) by mh7.mail.rice.edu (Postfix) with ESMTP id BEC0B28F7D9; Tue, 7 Dec 2010 15:52:20 -0600 (CST) Received: by aart.is.rice.edu (Postfix, from userid 18612) id 8D6222D5A6; Tue, 7 Dec 2010 15:52:20 -0600 (CST) Date: Tue, 7 Dec 2010 15:52:20 -0600 From: Kenneth Marshall To: Tony Capobianco Cc: pgsql-sql@postgresql.org Subject: Re: concatenate question Message-ID: <20101207215220.GF4028@aart.is.rice.edu> References: <1291757825.16970.18.camel@tony1.localdomain> <1291758466.16970.19.camel@tony1.localdomain> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1291758466.16970.19.camel@tony1.localdomain> User-Agent: Mutt/1.5.16 (2007-06-09) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201012/38 X-Sequence-Number: 34874 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 > > 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 >