Get tables which are used by Materialized View in PostgreSQL

In PostgreSQL, you can use the pg_matviews system catalog table to get the tables used by a Materialized View. The pg_matviews table contains one row for each Materialized View in the current database, and it has a column named “matrelid” which references the OID of the table or query used by the Materialized View.

To get the tables used by a Materialized View, you can join the pg_matviews table with the pg_class table, which contains one row for each table or query in the current database. The pg_class table has a column named “relname” which contains the name of the table or query.

The following query will show the list of tables used by a Materialized View:

SELECT matviewname, relname as table_name
FROM pg_matviews
JOIN pg_class ON pg_matviews.matrelid = pg_class.oid;

The above query will give you the name of the Materialized View and the table used by it.

You can also filter the query by the name of the Materialized View by adding a WHERE clause to the query

SELECT matviewname, relname as table_name
FROM pg_matviews
JOIN pg_class ON pg_matviews.matrelid = pg_class.oid
WHERE matviewname = 'your_materialized_view_name';

It will give you the table name used by the specific Materialized view