Steps to Change a PostgreSQL Column Datatype when Included in a View

By: Andrea Gnemmi | Updated: 2024-08-16 | Comments | Related: > PostgreSQL

Problem

We have all encountered the need to change a column data type in a table. Unfortunately, in PostgreSQL, you cannot change it in a single step if the column is used in a view. In this article, we cover the steps to change the data type for a column in a PostgreSQL table.

Solution

In PostgreSQL, if a table's column has dependencies, i.e., it is used in a view, you will get an error if you try to change the data type or even the length. In this tip, we will see a workaround and some way to script out all the dependencies of a column/table.

PostgreSQL Behavior

To explain PostgreSQL behavior, let's perform a little test using a test database, Chinook, a GitHub free downloadable database available in multiple RDBMS formats. It is a simulation of a digital media store with some sample data. All you have to do is download the version you need, and you will have all the scripts for data structure and all the Inserts for data.

We already created some views in one of my previous tips: SQL VIEW Comparison in SQL Server, Oracle and PostgreSQL. We will reuse one of them but add some complexity. So, this example will illustrate one of the worst practices I always advise against (but have encountered many times in my working life): create a view based on another view!

First, we repeat the script for the original view:

--MSSQLTips.com
 
CREATE or replace VIEW "Finance".view_Invoice_Genre
AS 
SELECT "Finance"."Invoice"."InvoiceId", "InvoiceDate", SUM("InvoiceLine"."UnitPrice"*"Quantity") AS Total_by_Genre, 
"Genre"."Name" AS Genre
FROM "Finance"."Invoice"
INNER JOIN "Finance"."InvoiceLine" ON "InvoiceLine"."InvoiceId" = "Invoice"."InvoiceId"
INNER JOIN "Track" ON "Track"."TrackId" = "InvoiceLine"."TrackId"
INNER JOIN "Genre" ON "Genre"."GenreId" = "Track"."GenreId"
GROUP BY "Invoice"."InvoiceId",
         "InvoiceDate",
         "Genre"."Name";

And now, we create another view based on this one:

--MSSQLTips.com
 
create or replace view "Finance".view_invoice_second_level
   as
   select genre, sum(Total_by_genre) as total
   from "Finance".view_invoice_genre
   group by genre;

Now we can try to modify one column of table Genre that is used in the view by doing an ALTER TABLE:

--MSSQLTips.com
 
alter table "Genre" alter column "Name" type varchar(200);

As expected, we received an error:

The error is self-explanatory: we cannot modify a column that is used in a view because it has a dependency. However, if you read the error message closely, it refers to a rule, and the official documentation states:

"Views in PostgreSQL are implemented using the rule system. A view is basically an empty table (having no actual storage) with an ON SELECT DO INSTEAD rule. Conventionally, that rule is named _RETURN."

If we do a simple query on catalog pg_rewrite, we can see the rule associated with this view:

--MSSQLTips.com
 
select rulename, ev_class::regclass as relation_name, ev_action
from pg_rewrite
   where ev_class='"Finance".view_invoice_genre'::regclass;

So, we cannot modify it, as PostgreSQL treats the view almost like a table. The only way to modify the column is to drop the view, issue the ALTER TABLE, and then recreate the view. Obviously, before dropping the view, it is necessary to save the CREATE VIEW script of the view. But this is not all. We also need to save the privileges given to different users on the view.

Below, we have a simple way to do this using pgAdmin GUI. Right-click on the view name and select Scripts/CREATE SCRIPT option:

This way, we not only have the CREATE VIEW script but also all the GRANT scripts:

--MSSQLTips.com
 
-- View: Finance.view_invoice_genre
 
-- DROP VIEW "Finance".view_invoice_genre;
 
CREATE OR REPLACE VIEW "Finance".view_invoice_genre
 AS
 SELECT "Invoice"."InvoiceId",
    "Invoice"."InvoiceDate",
    sum("InvoiceLine"."UnitPrice" * "InvoiceLine"."Quantity"::numeric) AS total_by_genre,
    "Genre"."Name" AS genre
   FROM "Finance"."Invoice"
     JOIN "Finance"."InvoiceLine" ON "InvoiceLine"."InvoiceId" = "Invoice"."InvoiceId"
     JOIN "Track" ON "Track"."TrackId" = "InvoiceLine"."TrackId"
     JOIN "Genre" ON "Genre"."GenreId" = "Track"."GenreId"
  GROUP BY "Invoice"."InvoiceId", "Invoice"."InvoiceDate", "Genre"."Name";
 
ALTER TABLE "Finance".view_invoice_genre
    OWNER TO postgres;

In this case, we do not have any GRANTs, but we have a script to give the correct ownership to the view. Note: This last ALTER TABLE command demonstrates again that PostgreSQL treats views like tables.

At this point, we are ready to drop the view and issue the original ALTER TABLE to modify the column Name in table Genre, but are we? Not quite, I'd say; let's see why.

We try to issue the delete of the view directly from pgAdmin GUI with a right-click and Delete, but we get this result:

As expected, we cannot drop the view as it is used in another one. As you may have noticed in the pgAdmin GUI menu, there are two voices, Delete and Delete Cascade:

If we use the second option (Delete (Cascade)), we can delete both views. Unfortunately, we do not have a CREATE script prepared for the other view. This can become a very time-consuming task in the case of nested views, with the possibility of losing some view definitions. That's when a couple of scripts on the catalog come to help!

With the following script, we can find all the views scripts that depend on a specific table and column, as well as all the nested views depending on it:

--MSSQLTips.com
 
WITH RECURSIVE views AS (
SELECT viste.oid::regclass AS view, viste.relkind = 'm' AS is_materialized
FROM pg_attribute AS colonne
JOIN pg_depend AS depend 
ON colonne.attnum=depend.refobjsubid AND colonne.attrelid=depend.refobjid
JOIN pg_rewrite AS rules 
ON depend.objid=rules.oid
JOIN pg_class AS viste
ON rules.ev_class=viste.oid
WHERE viste.relkind IN ('v', 'm') AND depend.classid = 'pg_rewrite'::regclass AND depend.refclassid = 'pg_class'::regclass 
AND depend.deptype = 'n' AND colonne.attrelid = '"Genre"'::regclass AND colonne.attname = 'Name' --Column name without ""
UNION --not UNION ALL as we need the views that depend on these, no repetitions
SELECT viste.oid::regclass,viste.relkind = 'm'
FROM views
JOIN pg_depend AS depend
ON views.view=depend.refobjid 
JOIN pg_rewrite AS rules  
ON depend.objid=rules.oid
JOIN pg_class AS viste
ON rules.ev_class=viste.oid
WHERE viste.relkind IN ('v', 'm') AND depend.classid = 'pg_rewrite'::regclass AND depend.refclassid = 'pg_class'::regclass
AND depend.deptype = 'n' AND viste.oid <> views.view -- no loop
)
SELECT format('CREATE%s %s AS%s',
              CASE WHEN is_materialized
                   THEN ' MATERIALIZED VIEW IF NOT EXISTS'
                   ELSE ' OR REPLACE VIEW'
              END, 
              view, 
              pg_get_viewdef(view))
FROM views
GROUP BY view, is_materialized;

Let's take a moment to explain this query before we look at the results.

First of all, we make use of a recursive CTE in order to get the views depending on the table and column that we put in the WHERE clause. In the CTE, we also have a UNION to get all the views depending on the first ones. We also extract from the pg_class if the view is materialized or not. At the end, we query the results of the CTE to obtain the view definition and build the script using the pg_get_viewdef function. Pay attention to the fact that we use the information flag is_materialized, which we extracted to have the correct script using the format() function.

We make use of PostgreSQL's proprietary catalogs (pg_class, pg_attribute, pg_depend, and pg_rewrite) to obtain all the needed information.

Another important note is the use of ::regclass to convert an oid number to the corresponding database object name, but also vice versa as in this case:

AND colonne.attrelid = '"Genre"'::regclass.

Now we are ready to see the resulting scripts:

As expected, we have our first view and nested view, as well as a materialized view that I created previously, which is also based on the same table and column.

We can now copy the results into another query window:

--MSSQLTips.com
 
CREATE or replace VIEW "Finance".view_invoice_genre AS SELECT "Invoice"."InvoiceId",
    "Invoice"."InvoiceDate",
    sum(("InvoiceLine"."UnitPrice" * ("InvoiceLine"."Quantity")::numeric)) AS total_by_genre,
    "Genre"."Name" AS genre
   FROM ((("Finance"."Invoice"
     JOIN "Finance"."InvoiceLine" ON (("InvoiceLine"."InvoiceId" = "Invoice"."InvoiceId")))
     JOIN "Track" ON (("Track"."TrackId" = "InvoiceLine"."TrackId")))
     JOIN "Genre" ON (("Genre"."GenreId" = "Track"."GenreId")))
  GROUP BY "Invoice"."InvoiceId", "Invoice"."InvoiceDate", "Genre"."Name";
CREATE or replace VIEW "Finance".view_invoice_second_level AS SELECT genre,
    sum(total_by_genre) AS total
   FROM "Finance".view_invoice_genre
  GROUP BY genre;
CREATE MATERIALIZED VIEW IF NOT EXISTS "Finance".view_invoice_genre_materialized AS SELECT "Invoice"."InvoiceId",
    "Invoice"."InvoiceDate",
    sum(("InvoiceLine"."UnitPrice" * ("InvoiceLine"."Quantity")::numeric)) AS total_by_genre,
    "Genre"."Name" AS genre
   FROM ((("Finance"."Invoice"
     JOIN "Finance"."InvoiceLine" ON (("InvoiceLine"."InvoiceId" = "Invoice"."InvoiceId")))
     JOIN "Track" ON (("Track"."TrackId" = "InvoiceLine"."TrackId")))
     JOIN "Genre" ON (("Genre"."GenreId" = "Track"."GenreId")))
  GROUP BY "Invoice"."InvoiceId", "Invoice"."InvoiceDate", "Genre"."Name";

Now we have all the view definitions. But we are still missing something: the GRANT scripts to all the privileges assigned on these views!

For that purpose, we have another script based on the first one but using another catalog at the end:

--MSSQLTips.com
 
WITH RECURSIVE views AS (
SELECT viste.oid::regclass AS view
FROM pg_attribute AS colonne
JOIN pg_depend AS depend 
ON colonne.attnum=depend.refobjsubid AND colonne.attrelid=depend.refobjid  
JOIN pg_rewrite AS rules 
ON depend.objid=rules.oid
JOIN pg_class AS viste
ON viste.oid = rules.ev_class
WHERE viste.relkind IN ('v', 'm') AND depend.classid = 'pg_rewrite'::regclass AND depend.refclassid = 'pg_class'::regclass 
AND depend.deptype = 'n' AND colonne.attrelid = '"Genre"'::regclass AND colonne.attname = 'Name'
UNION
SELECT viste.oid::regclass
FROM views
JOIN pg_depend AS depend
ON views.view=depend.refobjid
JOIN pg_rewrite AS rules  
ON depend.objid=rules.oid
JOIN pg_class AS viste
ON rules.ev_class=viste.oid
WHERE viste.relkind IN ('v', 'm') AND depend.classid = 'pg_rewrite'::regclass  AND depend.refclassid = 'pg_class'::regclass
AND depend.deptype = 'n' AND viste.oid <> views.view  -- no loop
)
SELECT 'grant '||privilege_type||' on '||table_schema||'.'||table_name||' to '||grantee||';'
FROM information_schema.table_privileges
inner join views v
on table_schema||'.'||table_name=replace(v.view::varchar,'"','')
WHERE grantee<>table_schema;

Again, here are a few words on the query. The recursive CTE is almost the same; we are not extracting the information if it is a materialized view or not, as it is not needed, as well as the final GROUP BY. We use another catalog, this time an ANSI standard, under information_schema, which is table_privileges. In this case, we must also get rid of the "in the view name" after converting it from regclass to varchar in order to join with the catalog.

We can now take a look at the results:

As said before, these GRANTS to user postgres are due to the fact that postgres is the owner of all these views and materialized view, so that translates to basically a GRANT ALL ON.

Now that we have all the scripts ready, we can finally use the Delete (Cascade) option on the view Finance.view_invoice_genre and then change the column definition of Name on the table Genre:

We also need to delete the materialized view based on the same table:

And we are finally able to issue the alter table script:

alter table "Genre" alter column "Name" type varchar(200);

Now we can use the script for rebuilding the views and materialized view and then issue again all the grants:

--MSSQLTips.com
 
CREATE or replace VIEW "Finance".view_invoice_genre AS SELECT "Invoice"."InvoiceId",
    "Invoice"."InvoiceDate",
    sum(("InvoiceLine"."UnitPrice" * ("InvoiceLine"."Quantity")::numeric)) AS total_by_genre,
    "Genre"."Name" AS genre
   FROM ((("Finance"."Invoice"
     JOIN "Finance"."InvoiceLine" ON (("InvoiceLine"."InvoiceId" = "Invoice"."InvoiceId")))
     JOIN "Track" ON (("Track"."TrackId" = "InvoiceLine"."TrackId")))
     JOIN "Genre" ON (("Genre"."GenreId" = "Track"."GenreId")))
  GROUP BY "Invoice"."InvoiceId", "Invoice"."InvoiceDate", "Genre"."Name";
CREATE or replace VIEW "Finance".view_invoice_second_level AS SELECT genre,
    sum(total_by_genre) AS total
   FROM "Finance".view_invoice_genre
  GROUP BY genre;
CREATE MATERIALIZED VIEW IF NOT EXISTS "Finance".view_invoice_genre_materialized AS SELECT "Invoice"."InvoiceId",
    "Invoice"."InvoiceDate",
    sum(("InvoiceLine"."UnitPrice" * ("InvoiceLine"."Quantity")::numeric)) AS total_by_genre,
    "Genre"."Name" AS genre
   FROM ((("Finance"."Invoice"
     JOIN "Finance"."InvoiceLine" ON (("InvoiceLine"."InvoiceId" = "Invoice"."InvoiceId")))
     JOIN "Track" ON (("Track"."TrackId" = "InvoiceLine"."TrackId")))
     JOIN "Genre" ON (("Genre"."GenreId" = "Track"."GenreId")))
  GROUP BY "Invoice"."InvoiceId", "Invoice"."InvoiceDate", "Genre"."Name";

--MSSQLTips.com
 
grant INSERT on "Finance".view_invoice_genre to postgres;
grant SELECT on "Finance".view_invoice_genre to postgres;
grant UPDATE on "Finance".view_invoice_genre to postgres;
grant DELETE on "Finance".view_invoice_genre to postgres;
grant TRUNCATE on "Finance".view_invoice_genre to postgres;
grant REFERENCES on "Finance".view_invoice_genre to postgres;
grant TRIGGER on "Finance".view_invoice_genre to postgres;
grant INSERT on "Finance".view_invoice_second_level to postgres;
grant SELECT on "Finance".view_invoice_second_level to postgres;
grant UPDATE on "Finance".view_invoice_second_level to postgres;
grant DELETE on "Finance".view_invoice_second_level to postgres;
grant TRUNCATE on "Finance".view_invoice_second_level to postgres;
grant REFERENCES on "Finance".view_invoice_second_level to postgres;
grant TRIGGER on "Finance".view_invoice_second_level to postgres;

Next Steps

In this tip, we have seen the peculiar way PostgreSQL treats views and figured out how to get all depending views and materialized views of a table and column. The two scripts we reviewed can be easily adapted to obtain more or less information. I also want to remark on two very important points about SQL best practices that we have seen:

If possible, avoid using nested views, as it complicates the queries greatly.
In PostgreSQL, avoid naming a database object with uppercase letters. The PostgreSQL standard is for all calls to be lowercase. As we have seen, it complicates every query because we must always use the " in order to correctly refer to these names. For the same and other reasons, ALWAYS avoid using reserved SQL names. If you think that this recommendation is superfluous, please believe me when I say that I encountered more than once in my career table names such as GROUP… and imagine the consequences for everyone involved!

As always, a few links to the official documentation:

Other tips on views and finding dependencies:

About the author

Andrea Gnemmi is a Database and Data Warehouse professional with almost 20 years of experience, having started his career in Database Administration with SQL Server 2000.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-08-16