Pivotal Knowledge Base

Follow

How to cast Boolean to Text

Environment

 Product  Version
 Pivotal Greenplum  All Versions

Purpose

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...                         ^

Cause

Greenplum does not support interpreting cstring to other data types unless you write an 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)

Comments

Powered by Zendesk