Обсуждение: How to cast a char[] to varchar?
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/ ***-*--*----*-------*------------*--------------------*---------------
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?
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/ ***-*--*----*-------*------------*--------------------*---------------
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
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/ ***-*--*----*-------*------------*--------------------*---------------