Mailing-List: contact user-help@hawq.incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hawq.incubator.apache.org
MIME-Version: 1.0
From: Dominik Choma <dominik.choma@gmail.com>
Date: Wed, 10 Aug 2016 11:13:04 +0200
Message-ID: <CALTdvCRY13B=LNER-af_8OwoizDxUgULPWKgqP73zPVVQU+ZQA@mail.gmail.com>
Subject: cast from TEXT filed
To: user@hawq.incubator.apache.org
Content-Type: multipart/alternative; boundary=94eb2c066f3a8eb2080539b40ef7
archived-at: Wed, 10 Aug 2016 09:13:10 -0000

--94eb2c066f3a8eb2080539b40ef7
Content-Type: text/plain; charset=UTF-8

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

--94eb2c066f3a8eb2080539b40ef7
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi All,<div><br></div><div>I have some problems with strin=
g functions on _TEXT columns=C2=A0</div><div>After running =C2=A0madlib ass=
oc_rules =C2=A0function =C2=A0output data =C2=A0is stored at result table=
=C2=A0</div><div><br></div><div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 CREATE TAB=
LE {0}.assoc_rules</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (</d=
iv><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ruleId =C2=A0 =C2=A0 =C2=
=A0INT,</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 pre =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 TEXT[],</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 post =C2=A0 =C2=A0 =C2=A0 =C2=A0TEXT[],</div><div>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 support =C2=A0 =C2=A0 FLOAT8,</div><div>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 confidence =C2=A0FLOAT8,</div><div>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 lift =C2=A0 =C2=A0 =C2=A0 =C2=A0FLOAT8,</di=
v><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 conviction =C2=A0FLOAT8</d=
iv><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 )</div><div>=C2=A0 =C2=A0=
 =C2=A0 =C2=A0 DISTRIBUTED BY (ruleId)</div></div><div><br></div><div>I nee=
d to do some text operations like regexp_replace on result column.</div><di=
v>in postgreSQL That will do the trick :</div>select<br>regexp_replace(pre:=
:varchar(10000),&#39;{|}&#39;,&#39;&#39;,&#39;g&#39;)<br>from md.assoc_rule=
s;<div><br><div>but in HAWQ i get error</div><div>[42846] ERROR: cannot cas=
t type text[] to character varying</div><div><br></div><div><br></div><div>=
Is there a way to cast from text[] column to varchar ?</div><div>Thanks</di=
v><div><br></div><div>Dominik<br><div><div><br></div><div><br></div><br></d=
iv></div></div></div>

--94eb2c066f3a8eb2080539b40ef7--