How To Alter a Column Used By A View or Rule
Tags: postgres, database, csharp, fluentmigrator
Published: 2021-01-13 13:03:53

---
title: "How To Alter a Column Used By A View or Rule"
published: true
description: "PostgreSQL errors with cannot alter type of a column used by a view or rule when modifying table column definitions for a table having dependencies."
tags: ["postgres", "databases", "csharp",  "fluentmigrator"]
canonical_url: https://kags.me.ke/post/how-to-alter-a-column-used-by-a-view-or-rule/
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/r1rcpk471sov698jrvjd.png
---

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

```sql
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](https://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis). 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.

```sql
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](https://fluentmigrator.github.io/) database migration framework, exclude the `BEGIN;` and `COMMIT;` statements - otherwise the migration will fail with an error.

```c#
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](https://stackoverflow.com/questions/3243863/problem-with-postgres-alter-table/49000321#answer-49000321) 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.

```sql
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](https://pretius.com/postgresql-stop-worrying-about-table-and-view-dependencies/) details how these functions work. The post includes links to an [sql fiddle](http://sqlfiddle.com/#!15/e1e32/1) and a up to date [gist](https://gist.github.com/mateuszwenus/11187288).

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.