Alter table generated text column to normal text column

Question

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');

-- [42601] 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.

Answer 1

alter table product_history alter column name DROP EXPRESSION;

This form of alter table turns a stored generated column into a normal base column. Was added in postgresql 13.

Answer 2

As a_horse_with_no_name pointed out, this isn't possible. For my use case, I found a suitable workaround. In the statement-level trigger, instead of INSERT INTO foo SELECT * FROM ..., I used the pg_catalog to find all non-generated columns to execute a dynamic query.

Here's a sketch:

CREATE FUNCTION history_trigger() RETURNS trigger AS
$fn$
DECLARE
  curr_tbl text := tg_table_name;
  past_tbl text := replace(curr_tbl, '_current', '_past');
  ins_cols text;
BEGIN
  -- Find all the non-generated columns we'll insert.
  EXECUTE $$
    SELECT
      string_agg(attname, ', ') as str
    FROM pg_attribute
    WHERE attrelid = $1::regclass
      AND attnum > 0
      AND NOT attisdropped
      AND attgenerated = ''
    GROUP BY attrelid;
  $$ INTO STRICT ins_cols USING past_tbl;
  IF ins_cols = '' || ins_cols IS NULL THEN
    RAISE EXCEPTION 'columns to insert not found for table %', past_tbl;
  END IF;

  -- Insert all modified rows.
  EXECUTE format($$
    INSERT INTO %I (%s)
    SELECT %s
    FROM old_table;
  $$, past_tbl, ins_cols, ins_cols);

  -- Trigger functions invoked by per-statement triggers should always return NULL.
  -- https://www.postgresql.org/docs/current/trigger-definition.html
  RETURN NULL;
END;
$fn$
  LANGUAGE plpgsql;