0

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.

2
  • This is not possible. You will have to drop the generated attribute completely and populate that column with a trigger Jul 15 at 5:20
  • Gotcha, I found a workaround I'll post.
    – Joe
    Jul 15 at 5:34
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.