CODEWITHSUNDEEP
sqldatabasepostgresql
ruciu
ruciu

Reputation: 712

PostgreSQL - Cast generated column type

I tried to create generated column from other JSON type column with ALTER TABLE "Invoice" ADD COLUMN created Integer GENERATED ALWAYS AS (data ->> 'created') STORED;

When I execute this I get error ERROR: column "created" is of type integer but default expression is of type text HINT: You will need to rewrite or cast the expression. SQL state: 42804

I tried to cast it with CAST function and :: operator but with no lack. Is there any way to do it? Or maybe I should generate this column differently?

Thanks

Upvotes: 0

Views: 727

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

How about converting the value to an int?

ALTER TABLE "Invoice" ADD COLUMN created Integer
    GENERATED ALWAYS AS ( (data ->> 'created')::int ) STORED;

Upvotes: 1

Related Questions