Intermediate level · Module 3: Views, Constraints and Transactions · Lesson 2 of 3
Views
Save queries as views to simplify reporting and control what different users can see.
In this lesson you will
- Create and use views
- Use views to hide complexity and sensitive columns
Key concepts
CREATE VIEWreporting viewsecurity through viewsmaterialised views (overview)
The lesson
A view is a saved query that behaves like a table: CREATE VIEW distribution_summary AS SELECT … ;. Managers can query the view without knowing the joins behind it.
Views also control access: a view of households without names or phone numbers can be shared with analysts who do not need personal data.
Ordinary views run their query each time. PostgreSQL's materialised views store the results and are refreshed on demand, which helps with slow reports on large data.
Step by step
- Create a view of distributions per location.
- Query the view with filters and sorting.
- Create an anonymised view without personal data.
- Grant a reporting role access to the view only.
Practice activity
Create the reporting views for the NGO's programme managers and donors.
Expected result: Managers and donors can answer their questions from views that contain no unnecessary personal data.
Check your understanding
-
What is a view?
Show answer
A saved query that can be used like a table.
-
How can views protect personal data?
Show answer
By exposing only the columns a group of users needs.
Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.