Hello! Views are great for reporting purposes or refining access to sensitive data. In this article we will discuss how to create view PostgreSQL topic.

Creating views in PostgreSQL can significantly enhance the manageability and security of your database by providing a virtual table representing a result set of a stored query. This feature is particularly useful for simplifying complex data, customizing data access for different users or applications, and improving overall database performance.

When creating a PostgreSQL view, you essentially define a saved query that can be treated as a table within the database. This allows you to conveniently segment your data and control access to sensitive information while offering greater flexibility for reporting and analytics purposes.

To create a view in PostgreSQL, you typically use the CREATE VIEW statement, specifying the view name and the underlying query that defines the view’s result set. Once the view is created, it can be queried like a regular table, providing a simplified and secure way to interact with the data.

In the context of this article, we’ll delve into the intricacies of creating views in PostgreSQL, exploring the syntax and best practices to ensure you can effectively utilize this powerful feature for your data management and reporting needs.

Views are created like other objects in PostgreSQL. We create views with SQL statements, which use other tables or views as the data source.

What is a PostgreSQL View?

In short form, a view is a logical table. We use view a shortcut to display data gathered from multiple tables or views.

Views don’t store data physically, they are being used as a shortcut to access cumulative data stored physically in multiple tables.

If you create a view with default parameters, it only support SELECT from that view, but PostgreSQL also supports inserting and updating data to view as well.

When to Use Views?

Those two business requirements are the main reasons to use views.

Using Views for Reporting

Assume that, there is a demand for some reports to be created periodically. Every Thursday, someone from the finance team calls you and asks for a report.

And you copy the query from your archive, past to editor, run and share the result with finance team.

Instead of doing this you can create a view and let finance team to access the view. Or, even there is a better option. You can show them how can they use PostgreSQL ODBC driver to connect Postgresql from Excel.

Using Views for Hiding Sensitive Data

A business contains sensitive data and sometimes (even not suggested) sensitive data is saved beside insensitive data.

According to GDPR regulations, we are obligated to restrict access to sensitive data. Utilizing a view presents an effective solution in this regard. By granting access to a view, individuals can access the necessary information without directly accessing personal data.

How to Create View PostgreSQL?

Preparing sample table structure and data. Let’s assume we have two tables, one for storing city population, and the other one is name of the city and name of the major. Start with population table:

CREATE TABLE public.cities (
	id int4 NOT NULL GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE),
	"name" varchar(100) NOT NULL,
	CONSTRAINT cities_pk PRIMARY KEY (id)
);

CREATE TABLE public.city_population (
	id int4 NOT NULL GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE),
    city_id int4 NULL,
	population int8 NULL,
		CONSTRAINT city_population_pk PRIMARY KEY (id)
);
ALTER TABLE public.city_population ADD CONSTRAINT city_population_cities_fk FOREIGN KEY (city_id) REFERENCES public.cities(id);

Let’s create another table for majors.

CREATE TABLE public.city_major (
	id int8 NOT NULL GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE),
	city_id int4 NOT NULL,
	major varchar(100) NULL,
	CONSTRAINT city_major_pk PRIMARY KEY (id)
);

ALTER TABLE public.city_major ADD CONSTRAINT city_major_cities_fk FOREIGN KEY (city_id) REFERENCES public.cities(id);

And insert sample data to our tables.

INSERT INTO public.cities
("name")
VALUES
	('Ankara'),
	('İstanbul'),
	('İzmir'),
	('Adana'),
	('Mersin');

INSERT INTO public.city_population
(population, city_id)
VALUES
(5663000, 1),
(15460000, 2),
(4367000, 3),
(1769000, 4),
(1814000, 5);

INSERT INTO public.city_major
(city_id, major)
VALUES
(1, 'Mansur Yavaş'),
(2, 'Ekrem İmamoğlu'),
(3, 'Tunç Soyer'),
(4, 'Zeydan Karalar'),
(5, 'Vahap Seçer');

Create a Filtering View

let’s start with defining a filtering view with population > 4.000.000

CREATE VIEW cities_population_gt4m AS SELECT * FROM city_population WHERE population > 4000000;

And result is

As you can see we can use a view as a data filter.

Creating a Reporting Filter

We can use a filter to join all the tables and create a one big table and use it as a report.

CREATE VIEW cities_report AS select C.id, CM.major, cp.population  FROM cities c, city_major cm, city_population cp  WHERE C.id = cm.city_id and cp.city_id = c.id;

As result of querying cities_report is:

Summary

In this article we’ve covered how to create view in PostgreSQL topic. In addition we talked about usage, purpose of the views. For more information you may want to check my article about refreshing materialized view concurrently article.

If you have any questions at all, please don’t hesitate to leave a comment. Your input is highly valued, so if there’s anything you’d like to contribute to this article, please do so by leaving a comment below. Your thoughts and feedback are truly appreciated, and I would genuinely love to hear from you.

Hi! I’m an IT Specialist

I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.

Get in touch with me.

Get in touch