Say you need to list all views in your PostgreSQL database. There are a few ways to get a list of all views in a database. This article will show you how to get the job done.
1. Using SQL Query
Let’s have a look at the first way to show all views in a PostgreSQL database, using an SQL script:
SELECT table_schema, table_name
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY table_schema, table_name
The result of the given query will look like this:
2. Using psql
All of the code written in the above SQL query can be replaced by one simple command line in psql. You can just use the \dv command.
For more details like the view's defining query, you can use the following command \sv [view_name] .
3. Using ERBuilder Data Modeler
There’s another way to list all views in your database and that using ERBuilder Data Modeler which can list all views in the PostgreSQL database.
For more details like the view's defining query, you just have to double-click on the view name. From there you can get all details of the view as you can edit it, add a new one, or delete the needless one.
A view’s defining query is shown in the screenshot below.
How ERBuilder Helps
ERBuilder Data Modeler is a GUI data modeling tool that allows you to visualize, design and model databases by using entity relationship diagrams and automatically generates the most popular SQL databases including Amazon Redshift. Generate and share the data Model documentation with your team. Optimize your data model by using advanced features such as test data generation, schema compare and schema synchronization. Try now ERBuilder for 15 days FREE