For a computed column, how to conditionally convert a string to a date

I have a table with a date field where my import data is sometimes NULL for that column. Cockroach does not allow dates to be null. So, I import the date into a string, and want to convert that null to infinity via a computed column, otherwise convert the string to a date.

drop table if exists test;
create TABLE test (
  identifier            string  not null,
  lastshipdate_RAW      string,  -- can be null which is not a valid date value
  lastshipdate          date AS 
  ( case when lastshipdate_RAW is null  
        then date 'infinity' 
        else CAST(lastshipdate_RAW AS date) 
  end ) STORED

The above gets the error:

ERROR: incompatible value type: string::date: context-dependent operators are not allowed in computed column

Another idea was to use the postgres TO_DATE() function, but that is not (yet?) supported but would seem to be the same as a CAST anyway.

Is there a Cockroach-specific way to conditionally convert a string to a date in a computed column?

Server version: CockroachDB CCL v20.2.3 (x86_64-unknown-linux-gnu, built 2020/12/14 18:33:39, go1.13.14) (same version as client)


Unfortunately as of v20.2 you can no longer create a computed column that casts a string to a date type.

Good to know. I had a slack discussion, and we came up with an alternative. Although DATES can be NULL, cockroach import into does not allow a CSV to have the text phrase NULL in the date column unless you have with nullif='NULL' in the import into command line. My initial attempt to solve this was the computed column technique but as we discovered you can’t cast a string to a date. Solved via the nullif argument.