If I have a table with a generated text column, how do I alter the column to remove the generation expression to get a normal, not-generated text type? Here's the schema:
CREATE TABLE product_history ( product_id bigint, name text GENERATED ALWAYS AS ( 'some name' ) STORED );
What I'd like is a command to use generation by default but allow custom values. As of Postgres 13.3, I don't think that's possible. The default clause looks like it only applies to generated identity columns:
-- Command I want: ALTER TABLE product_history ALTER COLUMN name SET GENERATED BY DEFAULT;
Here's the insert query I want to work:
INSERT INTO product_history (product_id, name) VALUES (1, 'foo'); --  ERROR: cannot insert into column "name" -- Detail: Column "name" is a generated column.
I'm running Postgres 13.3.
The use case is creating a history table such that I can insert the transition table of statement-level trigger into the history table. I can't do a blind insert from a select because the transition table rows include the generated column.