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)