Alter table generated text column to normal text column
1
vote
2
answers
1339
views
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.
Asked by Joe
(179 rep)
Jul 15, 2021, 04:23 AM
Last activity: Nov 26, 2024, 10:00 AM
Last activity: Nov 26, 2024, 10:00 AM