How to cast Boolean to Text

Products

• VMware Tanzu Greenplum

Issue/Introduction

Running the following SQL query failed with the error below when looking for a solution for casting boolean to text:

DECLARE qgis_4 BINARY CURSOR FOR SELECT st_asbinary("location",'NDR'),ctid,"start_datetime"::text,"end_datetime"::text,"temperature"::text,"pressure"::text,boolout("rainfall")::text,"humidity"::text,"illuminance"::text,"uva"::text,"ubv"::text FROM "event"."weather_soratena" WHERE "location" && st_makeenvelope(136.92967584819169247,37.11712946054284856,137.3367917387353998,37.52424535108657011,4326) AND st_intersects(st_curvetoline("location"),st_makeenvelope(136.92967584819169247,37.11712946054284856,137.3367917387353998,37.52424535108657011,4326))

ERROR: cannot cast type cstring to text 
LINE 1: ...ture"::text,"pressure"::text,boolout("rainfall")::text,"humi...
                                                             ^

 

Environment

Cause

Greenplum does not support interpreting cstring to other data types unless you write a UDF to the conversion:

gpadmin=# select boolout('1')::text;
ERROR: cannot cast type cstring to text
LINE 1: select boolout('1')::text;
                             ^
gpadmin=# select '1'::bool::text;
ERROR: cannot cast type boolean to text
LINE 1: select '1'::bool::text;
                          ^

 

Resolution

We can use the BooleanToText (boolean) function to convert boolean to text as below:

gpadmin=# CREATE FUNCTION BooleanToText(boolean) RETURNS text STRICT IMMUTABLE LANGUAGE PLPGSQL AS $$ BEGIN IF $1 IS TRUE THEN RETURN 'true'; ELSE RETURN 'false'; END IF; END; $$;
CREATE FUNCTION
gpadmin=# CREATE CAST (Boolean AS Text) WITH FUNCTION BooleanToText(boolean);
CREATE CAST
gpadmin=# select '1'::bool::text;
 text
------
 true
(1 row)

For cast type cstring to text, we can use the boolin (cstring) function to convert cstring to boolean first, then use the above CAST (Boolean AS Text) to cast boolean as text, example as below:

gpadmin=# select boolin(boolout('1'::bool))::text;
 boolin 
--------
 true
(1 row)

Additional Information

+ Environment:

Pivotal Greenplum all versions