PostgreSQL - Cast generated column type

Question

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

Gordon Linoff · Accepted Answer · 2020-11-19 19:28:45Z

1

How about converting the value to an int?

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

answered Nov 19, 2020 at 19:28

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL - Cast generated column type

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related