Thread: Varchar concatenate fields as Char or Varchar, not Text

Really, I dont see the 3 varchar fields concatenated to a new field.

But do this....

(varchar1||varchar2||varchar3||newfield)::varchar

Best regards

MargaretGillon@chromalloy.com wrote:
>
> Last week I upgraded to postgresql 8.1.4 (YEAH!)  In my database I
> have a view which concatenates three varchar fields to a new field.
> With postgresql 7.2.3 the field resulting from the concatenation was a
> varchar, with postgresql 8.1.4 the new field is a text field. This is
> affecting all kinds of forms in my application. Is there a way I can
> get the output as char  or varchar? View code is below.
>
>
> CREATE OR REPLACE VIEW vweventsummary AS
> SELECT b.ltname, (c.refullname::text || d.enname::text) ||
> f.evname::text AS evlinkname1,
> (g.refullname::text || h.enname::text) || i.evname::text AS evlinkname2,
> a.evid, a.evlinktype, a.eventity1, a.evevent1, a.evresource1,
> a.eventity2, a.evevent2, a.evresource2
> FROM event a, linktype b, resource c, entity d, event f, resource g,
> entity h, event i
> WHERE a.evlinktype = b.ltid AND a.evevent1 = f.evid AND
> a.evevent2 = i.evid AND a.evresource1 = c.reid
> AND a.evresource2 = g.reid AND a.eventity1 = d.enid AND a.eventity2 =
> h.enid;
>
>
> *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
> *** *** ***
> Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297
>
> This e-mail message and any attachment(s) are for the sole use of the
> intended recipient(s) and may contain proprietary and/or confidential
> information which may be privileged or otherwise protected from
> disclosure.  Any unauthorized review, use, disclosure or distribution
> is prohibited.  If you are not the intended recipient(s), please
> contact the sender by reply email and destroy the original message and
> any copies of the message as well as any attachment(s) to the original
> message.

Well, you cast all those fields to be concatenated to text. Why should the db
make a varchar out of that? I seriously doubt that 7.x made a varchar of that
- but then, 7.2 is very very old.
So either cast your fields to varchar (i.e. c.refullname::varchar ||
d.enname::varchar) or cast the result of the concatenation to a varchar.

UC

On Monday 07 August 2006 11:20, MargaretGillon@chromalloy.com wrote:
> Last week I upgraded to postgresql 8.1.4 (YEAH!)  In my database I have a
> view which concatenates three varchar fields to a new field. With
> postgresql 7.2.3 the field resulting from the concatenation was a varchar,
> with postgresql 8.1.4 the new field is a text field. This is affecting all
> kinds of forms in my application. Is there a way I can get the output as
> char  or varchar? View code is below.
>
>
> CREATE OR REPLACE VIEW vweventsummary AS
> SELECT b.ltname, (c.refullname::text || d.enname::text) || f.evname::text
> AS evlinkname1,
> (g.refullname::text || h.enname::text) || i.evname::text AS evlinkname2,
> a.evid, a.evlinktype, a.eventity1, a.evevent1, a.evresource1, a.eventity2,
> a.evevent2, a.evresource2
> FROM event a, linktype b, resource c, entity d, event f, resource g,
> entity h, event i
> WHERE a.evlinktype = b.ltid AND a.evevent1 = f.evid AND
> a.evevent2 = i.evid AND a.evresource1 = c.reid
> AND a.evresource2 = g.reid AND a.eventity1 = d.enid AND a.eventity2 =
> h.enid;
>
>
> *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
> *** ***
> Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297
>
> This e-mail message and any attachment(s) are for the sole use of the
> intended recipient(s) and may contain proprietary and/or confidential
> information which may be privileged or otherwise protected from
> disclosure.  Any unauthorized review, use, disclosure or distribution is
> prohibited.  If you are not the intended recipient(s), please contact the
> sender by reply email and destroy the original message and any copies of
> the message as well as any attachment(s) to the original message.

--
    UC

--
Open Source Solutions 4U, LLC    1618 Kelly St
Phone:  +1 707 568 3056        Santa Rosa, CA 95401
Cell:   +1 650 302 2405        United States
Fax:    +1 707 568 6416

SELECT b.ltname, ((c.refullname::text || d.enname::text) ||
f.evname::text)::varchar AS evlinkname1,

MargaretGillon@chromalloy.com wrote:
>
> >Rodrigo Gonzalez <rjgonzale@gmail.com> wrote on 08/07/2006 11:36:28 AM:
> > Really, I dont see the 3 varchar fields concatenated to a new field.
> >
> > But do this....
> >
> > (varchar1||varchar2||varchar3||newfield)::varchar
> >
> > Best regards
> >
>
> This is the concatenate statement
>
> > > SELECT b.ltname, (c.refullname::text || d.enname::text) ||
> > > f.evname::text AS evlinkname1,
>
> Margaret Gillon

On Mon, 2006-08-07 at 14:11, MargaretGillon@chromalloy.com wrote:
> >"Uwe C. Schroeder" <uwe@oss4u.com> wrote on 08/07/2006 11:30:28 AM:
> > Well, you cast all those fields to be concatenated to text. Why
> should the db
> > make a varchar out of that? I seriously doubt that 7.x made a
> varchar of that
> > - but then, 7.2 is very very old.
> > So either cast your fields to varchar (i.e. c.refullname::varchar ||
> > d.enname::varchar) or cast the result of the concatenation to a
> varchar.
> >
> > UC
>
> FYI in 8.1.4 if the cast is changed to varchar the result still comes
> out as a text field
> >> SELECT (c.refullname::varchar || d.enname::varchar ||
> f.evname::varchar) AS evlinkname1,
> results in evlinkname1 as text

Just FYI, text and varchar are, internally, pretty much the same types.
varchar has an optional precision setting as in varchar(200) while text
does not allow one.

All the text ops are written for text types, so varchar gets cast as
text (as do char types) before being operated on.

Note that you can cast the result of that select above to varchar:

SELECT (c.refullname || d.enname || f.evname)::varchar AS evlinkname1