Greenplum : Convert Datatype BYTEA to Text/Character

Question

Facing issue while Convert Datatype BYTEA to Text/Character

--select pg_catalog.decode('434F4D4D 4F4E', 'hex');

--select pg_catalog.encode('COMMON', 'hex');

--Query Table where col_1 has data type character varying and hex value is inserted

select col_1,pg_catalog.decode(col_1,'hex') from schema.table_1 limit 10

I have above table where i have inserted hex value in one of the column, now i want to update that, but it is giving me error

ERROR: column "col_1" is of type character varying but expression is of type bytea

LINE 1: update schema.table_1 set col_1 = pg_c...

HINT: You will need to rewrite or cast the expression.

********** Error **********

--Update Query

update schema.table_1 set col_1 = pg_catalog.decode(col_1,'hex')

Please help to resolve this

score 0 · Answer 1 · answered Dec 12 '14 at 07:24

Posted the same problem/query on linkedin group, and got one solution

select pg_catalog.encode(pg_catalog.decode('434F4D4D 4F4E', 'hex'), 'escape');

It will help to update the hex data no casting required.

I tried to update the table with above query, it works fine

Regards

Greenplum : Convert Datatype BYTEA to Text/Character

1 Answers1