PostgreSQL List Views

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!

mm

About Ubiq

Ubiq is a dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build dashboards & reports for your business. Try it for free today!