2014-04-22

PostgreSQL: How to handle table and view dependencies

PostgreSQL is very restrictive when it comes to modyfing existing objects. Very often when you try to ALTER TABLE or REPLACE VIEW it tells you that you cannot do it, because there's another object (typically a view or materialized view), which depends on the one you want to modify. It seems that the only solution is to DROP dependent objects, make desired changes to the target object and then recreate dropped objects.

2014-04-14

DB view dependencies in PostgreSQL

Introduction

I recently learned a clever trick in PostgreSQL, which allows you to check which DB views and tables are used by any view. It allows you to see view's dependency tree, for example:
public.v_top_department depends on public.v_department_sales_per_employee
  public.v_department_sales_per_employee depends on public.v_department_sales
    public.v_department_sales depends on public.departments
    public.v_department_sales depends on public.sales
  public.v_department_sales_per_employee depends on public.v_department_employees
    public.v_department_employees depends on public.departments
    public.v_department_employees depends on public.employees