CODEWITHSUNDEEP
postgresqlgenerated-columns
erchenstein
erchenstein

Reputation: 43

Postgres generated column cast Varchar to Date

I'am trying to use a generated column to cast a Varchar column to a Date column. The expression to generate the date consists of some string manipulation to derive an appropriate string that can then be casted to DATE. This is apparently not possible though, because - as the Postgres documentation about generated fields states - only immutable functions can be used in the expression to generate the column. If using a generated column in postgres with an expression containing a function such a CAST or TO_DATE you'll thus get the error : generation expression is not immutable

Postgres Documentation Generated Field

Current 'solution'

One possible solution, that works is to create an immutable version of TO_DATE and then use that in the expression to generate the values of the generated column. I'm not sure though wether this might have potential unwanted effects because TO_DATE is probably not usable in expressions for generated columns for a reason.

CREATE OR REPLACE FUNCTION immutable_to_date_fn(varchar) 
RETURNS DATE 
AS $$
BEGIN
    RETURN CAST(
        -- ......
    AS DATE);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Another solution is of course to just transform the string to date-format and than save it as varchar in the generated column.

Question

Does anyone know a way to cast a varchar to date in a generated column expression without having to create one's own function?

Upvotes: 0

Views: 60

Answers (1)

edd
edd

Reputation: 1417

Your alternative solution, rather than writing your own function, is to add a DATE column to the table and, on insert, calculate its value.

That is a common method in columnar DBs and related workload. So, not a sin.

Upvotes: 1

Related Questions