pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedconcatenate question
11+ messages / 7 participants
[nested] [flat]
* concatenate question
@ 2010-12-07 21:07 Tony Capobianco <tcapobianco@prospectiv.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tony Capobianco @ 2010-12-07 21:07 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* concatenate question
@ 2010-12-07 21:37 Tony Capobianco <tcapobianco@prospectiv.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tony Capobianco @ 2010-12-07 21:37 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: concatenate question
@ 2010-12-07 21:43 Peter Steinheuser <psteinheuser@myyearbook.com>
parent: Tony Capobianco <tcapobianco@prospectiv.com>
0 siblings, 1 reply; 11+ messages in thread
From: Peter Steinheuser @ 2010-12-07 21:43 UTC (permalink / raw)
To: Tony Capobianco <tcapobianco@prospectiv.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: concatenate question
@ 2010-12-07 21:47 Tony Capobianco <tcapobianco@prospectiv.com>
parent: Peter Steinheuser <psteinheuser@myyearbook.com>
0 siblings, 4 replies; 11+ messages in thread
From: Tony Capobianco @ 2010-12-07 21:47 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: concatenate question
@ 2010-12-07 21:51 Dmitriy Igrishin <dmitigr@gmail.com>
parent: Tony Capobianco <tcapobianco@prospectiv.com>
3 siblings, 0 replies; 11+ messages in thread
From: Dmitriy Igrishin @ 2010-12-07 21:51 UTC (permalink / raw)
To: Tony Capobianco <tcapobianco@prospectiv.com>; +Cc: pgsql-sql
Hey Tony,
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: concatenate question
@ 2010-12-07 21:52 Kenneth Marshall <ktm@rice.edu>
parent: Tony Capobianco <tcapobianco@prospectiv.com>
3 siblings, 0 replies; 11+ messages in thread
From: Kenneth Marshall @ 2010-12-07 21:52 UTC (permalink / raw)
To: Tony Capobianco <tcapobianco@prospectiv.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: concatenate question
@ 2010-12-07 21:53 Richard Broersma <richard.broersma@gmail.com>
parent: Tony Capobianco <tcapobianco@prospectiv.com>
3 siblings, 0 replies; 11+ messages in thread
From: Richard Broersma @ 2010-12-07 21:53 UTC (permalink / raw)
To: Tony Capobianco <tcapobianco@prospectiv.com>; +Cc: 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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: concatenate question
@ 2010-12-07 21:54 Peter Steinheuser <psteinheuser@myyearbook.com>
parent: Tony Capobianco <tcapobianco@prospectiv.com>
3 siblings, 1 reply; 11+ messages in thread
From: Peter Steinheuser @ 2010-12-07 21:54 UTC (permalink / raw)
To: Tony Capobianco <tcapobianco@prospectiv.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: concatenate question
@ 2010-12-08 14:08 Tony Capobianco <tcapobianco@prospectiv.com>
parent: Peter Steinheuser <psteinheuser@myyearbook.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tony Capobianco @ 2010-12-08 14:08 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: concatenate question
@ 2010-12-09 16:16 Edgardo Portal <egportal2002@yahoo.com>
parent: Tony Capobianco <tcapobianco@prospectiv.com>
0 siblings, 0 replies; 11+ messages in thread
From: Edgardo Portal @ 2010-12-09 16:16 UTC (permalink / raw)
To: pgsql-sql
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;'
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 ;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: concatenate question
@ 2010-12-11 13:42 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Tony Capobianco <tcapobianco@prospectiv.com>
0 siblings, 0 replies; 11+ messages in thread
From: Andreas Kretschmer @ 2010-12-11 13:42 UTC (permalink / raw)
To: pgsql-sql
Tony Capobianco <tcapobianco@prospectiv.com> wrote:
> Here's my table:
>
> plsql_dw=# \d tmpsv_parent_master
> Table "staging.tmpsv_parent_master"
>
> 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.
You can read? ;-)
You have to CAST the INT and the TIMESTAMP both into a STRING-typ, for
instance:
test=*# select 1::int || now()::timestamp;
ERROR: operator does not exist: integer || timestamp without time zone
ZEILE 1: select 1::int || now()::timestamp;
^
TIP: No operator matches the given name and argument type(s). You might need to add explicit type casts.
test=!# rollback;
ROLLBACK
Zeit: 0,124 ms
test=# select 1::text || now()::text;
?column?
--------------------------------
12010-12-11 14:42:12.571931+01
(1 Zeile)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2010-12-11 13:42 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-07 21:07 concatenate question Tony Capobianco <tcapobianco@prospectiv.com>
2010-12-11 13:42 ` Andreas Kretschmer <akretschmer@spamfence.net>
2010-12-07 21:37 concatenate question Tony Capobianco <tcapobianco@prospectiv.com>
2010-12-07 21:43 ` Peter Steinheuser <psteinheuser@myyearbook.com>
2010-12-07 21:47 ` Tony Capobianco <tcapobianco@prospectiv.com>
2010-12-07 21:51 ` Dmitriy Igrishin <dmitigr@gmail.com>
2010-12-07 21:52 ` Kenneth Marshall <ktm@rice.edu>
2010-12-07 21:53 ` Richard Broersma <richard.broersma@gmail.com>
2010-12-07 21:54 ` Peter Steinheuser <psteinheuser@myyearbook.com>
2010-12-08 14:08 ` Tony Capobianco <tcapobianco@prospectiv.com>
2010-12-09 16:16 ` Edgardo Portal <egportal2002@yahoo.com>
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