PostgreSQL views allow you to store SQL queries and call them for later use. Here’s how to list all views & tables in PostgreSQL using PostgreSQL List Views command.
How to List All Views in PostgreSQL
There is no built-in function to list all PostgreSQL views. So we will need to list them using SQL query.
Here is the SQL query to list all views & tables in PostgreSQL using PostgreSQL list views statement.
postgres=# select table_schema as schema_name,
table_name as view_name
from information_schema.views
where table_schema not in ('information_schema', 'pg_catalog')
order by schema_name,
view_name;
schema_name | view_name
-------------+---------------
public | order_view
public | sales_summary
The above query will list all views across all schema the user has access to. If you want to list views in specific schema only then mention it in your WHERE clause. For example, if you want to list views in test_schema schema, then here’s how to modify your WHERE clause for the same.
Bonus Read : How to Create View in PostgreSQL
postgres=# select table_schema as schema_name,
table_name as view_name
from information_schema.views
where table_schema in ('test_schema')
order by schema_name,
view_name;
Bonus Read : How to Get First Row Per Group in PostgreSQL
How to List all Tables in PostgreSQL
You can easily list all tables in your present database using \dt command
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+----------
public | exams | table | postgres
public | login | table | postgres
public | meeting | table | postgres
public | monthly_sales | table | postgres
public | product_sales | table | postgres
public | sales | table | postgres
public | sales_data | table | postgres
Bonus Read : How to Get Row Number in PostgreSQL
Here’s the SQL query if you want to list all tables across all databases that the user has access to.
SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
Hopefully, you can easily list views in PostgreSQL.
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
