Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to cast a char[] to varchar?

How to cast a char[] to varchar?

From
Jon Lapham
Date:
I have a column defined as "char(3)[]" which I would like to copy into a
different column defined as "varchar(255)".

I've tried to cast the char(3)[] field using "CAST( ... as varchar)" but
I get an error message, "Cannot cast type '_bpchar' to 'varchar'".

Any suggestions?

--

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
  Jon Lapham
  Extracta Moléculas Naturais, Rio de Janeiro, Brasil
  email: lapham@extracta.com.br      web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------

Re: How to cast a char[] to varchar?

From
Stephan Szabo
Date:
On Mon, 7 Jan 2002, Jon Lapham wrote:

> I have a column defined as "char(3)[]" which I would like to copy into a
> different column defined as "varchar(255)".
>
> I've tried to cast the char(3)[] field using "CAST( ... as varchar)" but
> I get an error message, "Cannot cast type '_bpchar' to 'varchar'".
>
> Any suggestions?

What are you trying to get out?  You've got a char(3) array, what do you
want the varchar to have in the end?

Re: How to cast a char[] to varchar?

From
Jon Lapham
Date:
Stephan Szabo wrote:

> On Mon, 7 Jan 2002, Jon Lapham wrote:
>
>
>>I have a column defined as "char(3)[]" which I would like to copy into a
>>different column defined as "varchar(255)".
>>
>>I've tried to cast the char(3)[] field using "CAST( ... as varchar)" but
>>I get an error message, "Cannot cast type '_bpchar' to 'varchar'".
>>
>>Any suggestions?
>>
>
> What are you trying to get out?  You've got a char(3) array, what do you
> want the varchar to have in the end?
>
>


Assuming "category" is the char(3)[] column, and "cat2" is the desired
varchar(255) column, I would like the following:


main_v0_8=# select peopleid, category, cat2 from people where
peopleid=100010;
  peopleid |   category    |  cat2
----------+---------------+---------
    100010 | {"col","dep"} | col dep
(1 row)

However, in the real database the char(3) array "category" may contain
anywhere from 0 to 10 items.

PS: It would be fine if the things in "cat2" contained the braces and
quotes, it does not need to be cleaned-up.

--

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
  Jon Lapham
  Extracta Moléculas Naturais, Rio de Janeiro, Brasil
  email: lapham@extracta.com.br      web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------

Re: How to cast a char[] to varchar?

From
Tom Lane
Date:
Jon Lapham <lapham@extracta.com.br> writes:
> I have a column defined as "char(3)[]" which I would like to copy into a
> different column defined as "varchar(255)".

> PS: It would be fine if the things in "cat2" contained the braces and
> quotes, it does not need to be cleaned-up.

You're going to need to do a little programming.  plpgsql provides about
the simplest solution, as it will happily try to convert anything to
anything else (if it can out-convert the source value to text and then
in-convert to the destination type without error, it's happy).  So:

regression=# create function to_varchar(char[]) returns varchar as '
regression'# begin
regression'#   return $1;
regression'# end;' language 'plpgsql';
CREATE
regression=# create table foo (f1 char(3)[]);
CREATE
regression=# insert into foo values ('{"col","dep"}');
INSERT 299666 1
regression=# insert into foo values ('{"fee","fi", "fo","fum"}');
INSERT 299667 1
regression=# select f1, to_varchar(f1) from foo;
          f1           |      to_varchar
-----------------------+-----------------------
 {col,dep}             | {col,dep}
 {fee,"fi ","fo ",fum} | {fee,"fi ","fo ",fum}
(2 rows)

If you wanted to be smarter --- like, say, getting rid of the braces and
so on --- you could code the conversion routine in pltcl or plperl,
either of which provide much better text-mashing capability than plpgsql
does.  I seem to recall that pltcl supports Postgres arrays better than
either of the others do, so it might be the best bet for this
particular task.

            regards, tom lane

Re: How to cast a char[] to varchar?

From
Jon Lapham
Date:
Tom Lane wrote:

> regression=# create function to_varchar(char[]) returns varchar as '
> regression'# begin
> regression'#   return $1;
> regression'# end;' language 'plpgsql';

Thanks Tom, works great.

--

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
  Jon Lapham
  Extracta Moléculas Naturais, Rio de Janeiro, Brasil
  email: lapham@extracta.com.br      web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------