How To Alter a Column Used By A View or Rule

In PostgreSQL, assume you have a table and view with the following definitions:

CREATE TABLE boq_items (
    id character varying(22) NOT NULL,
    item_no character varying(50) NOT NULL,
    activity_name character varying(255) NOT NULL,
    page_no int NOT NULL,
    qty numeric(14,2) NOT NULL,
    rate numeric(14,2) NOT NULL,
    bq_amt numeric(14,2) NOT NULL
);

CREATE VIEW vw_boq_item_names AS
    SELECT activity_name FROM boq_items;

Attempting to change the definition of activity_name column using ALTER TABLE boq_items ALTER activity_name TYPE text, ALTER activity_name SET NOT NULL; will return a cannot alter type of a column used by a view or rule. DETAIL: rule _RETURN on view vw_boq_item_names depends on column "activity_name" error . PostgreSQL will throw the same error if you attempt to change any column definition of the table.

PostgreSQL allows running DDL statements in a transaction. To resolve the error, we require to drop the view, run the alter statement and recreate the view but enclosing the these statements in a transaction.

BEGIN;
  DROP VIEW vw_boq_item_names;

  ALTER TABLE boq_items
    ALTER activity_name TYPE text,
    ALTER activity_name SET NOT NULL;

  CREATE VIEW vw_boq_item_names AS
    SELECT activity_name FROM boq_items;

COMMIT;

If you are using FluentMigrator database migration framework, exclude the BEGIN; and COMMIT; statements - otherwise the migration will fail with an error.

public override void Up()
{
  var sql = "DROP VIEW vw_boq_item_names;"  +

            "ALTER TABLE boq_items " +
              "ALTER activity_name TYPE text, " +
              "ALTER activity_name SET NOT NULL;" +

            "CREATE VIEW vw_boq_item_names AS " +
                "SELECT activity_name FROM boq_items;"

    Execute.Sql(sql);
}

In situations where a table has a lot of dependencies, or an object has cascading dependencies, a solution would be to create two functions - one to save the dependencies and the other to restore these dependencies. Each of these functions will require parameters for the schema and the object in the schema which has dependencies. Before changing the table and column definitions, call the function to save and drop the dependencies, make the definition changes and finally call the function to restore the dependencies.

select util.deps_save_and_drop_dependencies('mdm', 'global_item_master_swap');

alter table mdm.global_item_master_swap
alter column prod_id type varchar(128),
alter column prod_nme type varchar(512);

select util.deps_restore_dependencies('mdm', 'global_item_master_swap');

Stop worrying about table and view dependencies in PostgreSQL details how these functions work. The post includes links to an sql fiddle and a up to date gist.

I hope this will be helpful to anyone encountering cannot alter type of a column used by a view or rule error in PostgeSQL while updating table column definitions.

Comments

[Pacharapol Withayasakpunt]

What is util schema and mdm schema?