Re: Why cannot alter column type when a view depends on it?

> On Sep 28, 2025, at 21:18, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Sunday, September 28, 2025, Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com <mailto:li(dot)evan(dot)chao(at)gmail(dot)com>> wrote:
>>
>>
>> I tried to understand why this restriction is set, then I found that, when a function uses a view, the view can actually be dropped, only when the function is executed, it will raise an error saying the view doesn’t exist.
>
> I’m doubting this applies to SQL-standard body functions where dependencies are actually tracked on the objects the function references.
>
>>
>> In summary, this email is just raising the issue rather than proposing a solution. I want to hear feedbacks from the community. If people are interested in a solution to remove the restriction, then I can spend time on it.
>
> I’d rather spend the effort providing something in pg_dump where you can give it a object_id and pg_dump will produce the DDL needed to recreate all of the views/etc in the correct order and the drop commands as well. Making the alter table “just work” seems just too problematic and limited to justify spending time on IMO.
>

I agree doing limited checks and making it “just work” isn’t the right direction to go.

After researching, I think we can take the similar way against constant and index for view. After altering a column’s type, related constrains and indexes will be rebuilt. Column type change may also break constraints or indexes. For example, if an int typed column has a constraints of “check (a>0)”, then if you change the column type from int to text, the constraints will become invalid, so that rebuilding the constant will fail, as a result, alter column type will fail as well.

So, while altering a column type, we can also rebuild depended views. If rebuilding views succeeds, then alter column type also succeeds. This should be a reliable solution.

I am going to work on PoC with this approach.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/