Thanks! that's exactly what I need. Dominik
2016-08-10 11:49 GMT+02:00 Ruilong Huo <[email protected]>: > Hi Dominik, > > One workaround in HAWQ is to: 1) convert TEXT[] to TEXT using > array_to_string; 2) and then do regexp_replace on the TEXT; 3) convert TEXT > to TEXT[] to TEXT using string_to_array. Here is an example: > > *CREATE TABLE t (id INT, name TEXT[]);* > *CREATE TABLE* > > *INSERT INTO t VALUES (1, '{"abcd", "bcde"}'::TEXT[]);* > *INSERT 0 1* > > > *gptest=# SELECT * FROM t;* > * id | name* > *----+-------------* > * 1 | {abcd,bcde}* > *(1 row)* > > *SELECT string_to_array(regexp_replace(array_to_string(name, ','), > 'bcd','xyz','g'),',') FROM t;* > * string_to_array* > *-----------------* > * {axyz,xyze}* > *(1 row)* > > > Best regards, > Ruilong Huo > > On Wed, Aug 10, 2016 at 5:13 PM, Dominik Choma <[email protected]> > wrote: > >> Hi All, >> >> I have some problems with string functions on _TEXT columns >> After running madlib assoc_rules function output data is stored at >> result table >> >> CREATE TABLE {0}.assoc_rules >> ( >> ruleId INT, >> pre TEXT[], >> post TEXT[], >> support FLOAT8, >> confidence FLOAT8, >> lift FLOAT8, >> conviction FLOAT8 >> ) >> DISTRIBUTED BY (ruleId) >> >> I need to do some text operations like regexp_replace on result column. >> in postgreSQL That will do the trick : >> select >> regexp_replace(pre::varchar(10000),'{|}','','g') >> from md.assoc_rules; >> >> but in HAWQ i get error >> [42846] ERROR: cannot cast type text[] to character varying >> >> >> Is there a way to cast from text[] column to varchar ? >> Thanks >> >> Dominik >> >> >> >> >
