PostgreSQL View
Introduction
PostgreSQL View presents a streamlined way for seasoned or novice developers and DBAs to view the information from one or more base tables differently. Since base tables tend to contain a lot of information, views are especially beneficial for making complex queries to process through those lengthy sources.
Views also help manage user access to sections of table data. Administrators are able to do this by configuring a view to only show specific columns or rows in a base table. Learn how to create a view in PostgreSQL and increase your query-making productivity and views management today.
Prerequisites
- Install PostgreSQL for your OS, and then configure it. Next, run the application.
The benefits for users of PostgreSQL View
With views, users have many advantages:
They see data they have access to. There’s no need for administrators to worry about unauthorized users glimpsing at restricted data.
Data from one or many tables is organized in a clear, legible way.
Summary of tables enables report generation that is easy to comprehend.
Consistent views remain that way after updates occur to base tables.
Use CREATE VIEW to make a PostgreSQL View
- Start with a simple CREATE VIEW syntax like this:
1 2 3 4 | CREATE [TEMP | TEMPORARY] VIEW name_of_view AS SELECT col1, col2, col3..... FROM tableName WHERE condition; |
The above statement starts with CREATE VIEW.
After the CREATE part at the beginning of the statement, there is the TEMP and TEMPORARY parameters which mean the views are temporary and only for the session.
Next, the AS keyword is specified with, in this example, SELECT is the statement chosen and the columns or rows are selected. The query can be basic or complex.
The FROM clause is for identifying the name of the table from which the view is created.
The condition clause WHERE can be used at your discretion. It’s not required.
An example of how to construct a client info table for a PostgreSQL View
- The sample table below shows how to make a PostgreSQL table for the views. The
client_infotable contains typical client particulars such as their first and last name, age, and email address. Recreate this example table to test your views:
1 2 3 4 5 6 7 | CREATE TABLE client_info ( client_id SERIAL PRIMARY KEY, client_firstname TEXT, client_lastname TEXT, client_age INTEGER, client_email TEXT ); |
- Add fictitious information for the sample
client_infotable:
1 2 3 4 5 6 | INSERT INTO client_info (client_firstname, client_lastname, client_age, client_email) VALUES ('john', 'turf', 17, 'jturf@example.com'), ('raizel', 'mendez', 16, 'rmendez@example.com'), ('gwyneth', 'damon', 16, 'gdamon@example.com'), ('yeshua', 'galisanao', 8, 'ygalisanaof@example.com'), ('dereck', 'scott', 10, 'dscott@example.com'); |
- The result should look similar to this one here below:
1 2 3 4 5 6 7 | client_id | client_firstname | client_lastname | client_age | client_email -----------+------------------+-----------------+------------+------------------------ 1 | John | Turf | 17 | jturf@example.com 2 | Raizel | Mendez | 16 | rmendez@example.com 3 | Gwyneth | Damon | 16 | gdamon@example.com 4 | Yeshua | Galisanao | 8 | ygalisanao@example.com 5 | Dereck | Scott | 10 | dscott@example.com |
The client_info table is ready from which to draw a view.
Use this `CREATE VIEW statement followed by the SELECT clause to identify the columns for your view.
Select certain columns from the table for your PostgreSQL View.
Finally, you’ll choose the table where the information is located using the FROM clause.
Complete the above steps like this:
1 2 3 | CREATE VIEW client_view AS SELECT client_firstname, client_lastname, client_age FROM client_info; |
- Use the SELECT statement showing the same columns like the one you used above and make a
client_viewquery for your test database:
1 2 3 4 5 6 7 8 9 | testdatabase=# SELECT * FROM client_view; client_firstname | client_lastname | client_age ------------------+-----------------+------------ John | Turf | 17 Raizel | Mendez | 16 Gwyneth | Damon | 16 Yeshua | Galisanao | 8 Dereck | Scott | 10 (5 ROWS) |
The result is that the PostgreSQL View displays the columns you selected. It also counts the rows.
CONCLUSION
This tutorial explained how DBAs and developers can easily create a PostgreSQL View to display portions of one or more base tables differently. They accomplish this by utilizing the statement CREATE VIEW. Administrators can use views to help secure sensitive data by giving users limited access to table information. For example, they might want to create a view to show users only a few particular columns or rows they have permission to see.
Views do something else that’s beneficial for administrators who manage user access to table data. They streamline the querying process for queries that contain complex statements. Begin enjoying the advantages of creating views today.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started