Re: String concat issues in 8.3.1 (8.3.1 bug?)

kevin kempter wrote:
> I have a table as follows:
>
> \d test_dim
>                      Table "public.test_dim"
>      Column      |            Type             |       Modifiers
> -----------------+-----------------------------+------------------------
>  customer_srcid  | bigint                      | not null
>  segment_srcid      | bigint                      | not null
>  show_name       | character varying(500)      | not null
>  create_dt       | timestamp without time zone | not null default now()
>
>
> I have this table in a db on an 8.2.6 cluster and in another db on an
> 8.3.1 cluster.
>
> In both clusters I can do the following:
>
>
> # select customer_srcid,  segment_srcid from test_dim;
>  customer_srcid | segment_srcid
> ----------------+---------------
>               1 |             1
>               1 |             2
>               1 |             3
>               1 |
>               1 |
>               1 |

Are you sure the definition has "not null" on it?

> In version 8.2.6 I can do this:
>
> # select customer_srcid || segment_srcid from test_dim;
>  ?column?
> ----------
>  11
>  12
>  13

> However in the 8.3.1 cluster I get this (Both clusters have the same
> data set - via a pg_dump):
>
> # select customer_srcid || segment_srcid from test_dim;
> ERROR:  operator does not exist: bigint || bigint
> LINE 1: select customer_srcid || segment_srcid from sl_segment_dim;
>                               ^
> HINT:  No operator matches the given name and argument type(s). You
> might need to add explicit type casts.

Some of the implicit typecasting has been tightened up in 8.3 - try
casting to text first.

SELECT customer_srcid::text || segment_srcid::text ...

--
   Richard Huxton
   Archonet Ltd