Disclaimer: I am new to opaleye so might be misinterpreting what unsafeCoerceField/unsafeCast are supposed to be doing
I have a table countries which contains a national_day DATE value (nullable) and am trying to convert the value in the field to the string "Unknown" if it is NULL or cast it to a TEXT otherwise.
Essentially, I am trying convert this SQL query to opaleye -
SELECT name, COALESCE(CAST(national_day as TEXT), "Unknown") FROM countries;
This is the query I initially came up with, using unsafeCast
query :: Select (Field SqlText, Field SqlText)
query = do
(_,name,_,national_day,_,_,_) <- selectTable countries
let x = fromNullable (toFields "Unknown") (unsafeCast "TEXT" national_day)
pure (name,x)
This works as expected and generates the following SQL query -
SELECT
"name1_1" as "result1_2",
CASE WHEN (CAST("national_day3_1" AS text)) IS NULL THEN CAST(E'Unknown' AS text) ELSE CAST("national_day3_1" AS text) END as "result2_2"
FROM (SELECT
"country_id" as "country_id0_1",
"name" as "name1_1",
"area" as "area2_1",
"national_day" as "national_day3_1",
"country_code2" as "country_code24_1",
"country_code3" as "country_code35_1",
"region_id" as "region_id6_1"
FROM "countries" as "T1") as "T1"
However, the docs on hackage say that Opaleye.Column is to be deprecated and replaced with Opaleye.Field. The only function in Opaleye.Field which seems like it would do what I need is unsafeCoerceField, so I rewrote the above query like so -
query :: Select (Field SqlText, Field SqlText)
query = do
(_,name,_,national_day,_,_,_) <- selectTable countries
let x = fromNullable (toFields "Unknown") (unsafeCoerceField national_day)
pure (name,x)
However, this causes a runtime error as below -
*** Exception: SqlError {sqlState = "42804", sqlExecStatus = FatalError, sqlErrorMsg = "CASE types date and text cannot be matched", sqlErrorDetail = "", sqlErrorHint = ""}
And generates the following, incorrect, SQL query -
SELECT
"name1_1" as "result1_2",
CASE WHEN ("national_day3_1") IS NULL THEN CAST(E'Unknown' AS text) ELSE "national_day3_1" END as "result2_2"
FROM (SELECT
"country_id" as "country_id0_1",
"name" as "name1_1",
"area" as "area2_1",
"national_day" as "national_day3_1",
"country_code2" as "country_code24_1",
"country_code3" as "country_code35_1",
"region_id" as "region_id6_1"
FROM "countries" as "T1") as "T1"
Am I misunderstanding what unsafeCoerceField is supposed to be doing? It seems like it works only on the haskell type level and does not translate to a SQL CAST clause; If so, how would I go about casting SQL values into other SQL values through opaleye?
Disclaimer: I am new to opaleye so might be misinterpreting what
unsafeCoerceField
/unsafeCast
are supposed to be doingI have a table
countries
which contains anational_day DATE
value (nullable) and am trying to convert the value in the field to the string "Unknown" if it isNULL
or cast it to aTEXT
otherwise.Essentially, I am trying convert this SQL query to opaleye -
This is the query I initially came up with, using
unsafeCast
This works as expected and generates the following SQL query -
However, the docs on hackage say that
Opaleye.Column
is to be deprecated and replaced withOpaleye.Field
. The only function inOpaleye.Field
which seems like it would do what I need isunsafeCoerceField
, so I rewrote the above query like so -However, this causes a runtime error as below -
And generates the following, incorrect, SQL query -
Am I misunderstanding what
unsafeCoerceField
is supposed to be doing? It seems like it works only on the haskell type level and does not translate to a SQLCAST
clause; If so, how would I go about casting SQL values into other SQL values through opaleye?Thanks,