Varchar concatenate fields as Char or Varchar, not Text

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

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:

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

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:41:52 AM:

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

This worked, thank you.
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
*** ***
Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297

"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

I confirmed that 7.x created a varchar before I posted the question to the
list. If you doubt my statment I will provide screen shots. I am still
inexperienced with postgresql and am not sure what commands such as cast
can do/are doing which is why I posted the question to get help. The view
was written by another person. I did not know the concatenation result
could be cast or I would not have asked the question.

Regards,
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
*** ***
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.

"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

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

Scott Marlowe <smarlowe@g2switchworks.com> wrote on 08/07/2006 12:18:17

PM:

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

This is what another person , Rodrigo Gonzalez, also wrote for me to do
and it worked great. Thank you for explaining about the internal function
of text and varchar, it is always good to understand more.

Again, my thanks to everyone who gave me a solution.

Margaret Gillon