How to create a view in PostgreSQL

In this Postgresql tutorial, we are going to learn about “Postgresql create view“, will see how to create a view in PostgreSQL, examples of creating views from existing tables, databases in PostgreSQL. We will cover the following topics.

• Postgresql create view with parameters
• Postgresql create view if not exists
• Postgresql create view from multiple tables
• Postgresql create view from another database
• Postgresql create view with join
• Postgresql create view with calculated column
• Postgresql create view from another schema
• Postgresql create view union all
• Postgresql create view order by and group by
• Postgresql create view based on another view

What is a view in PostgreSQL

What is a view in Postgresql? The view is a like logical table or virtual table in Postgresql database, the view is created using existing tables and existing tables act as base tables for view.

There are two types of views in PostgreSQL:

• Temporary view
• Permanent view

PostgreSQL Temporary views are created on basis of temporary tables, and these tables are dropped once we log out from the current session, but permanent views persist in the Postgresql database.

How to create a view in PostgreSQL

Let us see how to create a view in PostgreSQL.

First, let’s learn about the syntax of creating a view.

CREATE OR REPLACE VIEW view_name (column_name) WITH AS query

In Postgresql, CREATE VIEW statement is used to create a view where view_name is the name of the view that we want to create.

• column_name: It is an optional list of names to be used for columns of the view. If it is not given, the column names are taken from the query.
• query: It is the SELECT statement that extracts data from base tables and inserts it into a view.

Let’s create two tables named dev_teams and dev and insert some of the data into both of the tables.

CREATE TABLE dev_teams (
id SERIAL PRIMARY KEY,
dev_team_count INTEGER,
team_department VARCHAR (100)
);


CREATE TABLE devs (
devs_id INTEGER NOT NULL,
devs_team_id INTEGER REFERENCES dev_teams (id),
devs_name VARCHAR (100),
devs_position VARCHAR (100),
devs_technology VARCHAR (100),
PRIMARY KEY (devs_id,devs_team_id)
);

In the above code, the devs table is for developers that will act as the child table. It will be one-to-many relationships between dev_teams and devs tables. devs_team_id will be our referencing key which will refer to the id of the dev_teams table.

Let’s insert some data in both tables.

INSERT INTO dev_teams (id, dev_team_count, team_department) VALUES('1','5','Accounting');
INSERT INTO dev_teams (id, dev_team_count, team_department) VALUES('2','6','Inventory');
INSERT INTO dev_teams (id, dev_team_count, team_department) VALUES('3','5','Human Resource');
INSERT INTO dev_teams (id, dev_team_count, team_department) VALUES('4','7','CRM');
INSERT INTO dev_teams (id, dev_team_count, team_department) VALUES('5','9','Bug Solver');
INSERT INTO dev_teams (id, dev_team_count, team_department) VALUES('6','4','Document');


INSERT INTO devs (devs_id, devs_team_id, devs_name, devs_position, devs_technology) VALUES(1,2,'Lucifer','Senior SD','Java');
INSERT INTO devs (devs_id, devs_team_id, devs_name, devs_position, devs_technology) VALUES(2,1,'Chloe','Developer','Angular');
INSERT INTO devs (devs_id, devs_team_id, devs_name, devs_position, devs_technology) VALUES(3,2,'Dan','Developer','Hibernate');
INSERT INTO devs (devs_id, devs_team_id, devs_name, devs_position, devs_technology) VALUES(4,3,'Rony','Support','Digital Marketing');
INSERT INTO devs (devs_id, devs_team_id, devs_name, devs_position, devs_technology) VALUES(5,3,'Jhon','Tester','Maven');

Let’s understand with the help of an example.

CREATE VIEW example_view AS (SELECT * FROM devs);

SELECT * FROM example_view;

In the above code, we are creating a view named example_view that consists of all records from the devs table.

Postgresql create a view with parameters

Create view statement has some parameters in Postgresql that we can use with it. The parameters are given below.

• Temp or Temporary: If it is specified, then the view is created as a temporary view. Note temporary views are automatically dropped after logout from the current session.
• Recursive: It is used to create a recursive view.

Let’s create a temporary view named data_view.

CREATE TEMPORARY VIEW data_view AS SELECT devs_name, devs_position 
FROM devs;

From the above code, we have created a view named data_view which is the temporary view, and this view contains records of devs_name, devs_postion columns that exist in the devs table.

Let’s see the records of the data_view table.

SELECT * FROM data_view;

This is how to create a view in PostgreSQL.

Read Postgresql change column data type

PostgreSQL create view if not exists

In PostgreSQL, there is no command like CREATE VIEW if not exists but we can accomplish this task with the help of Postgresql Procedural language. Let’s understand through examples.

Create a new view named example_view.

CREATE VIEW example_view AS SELECT devs_name,devs_technology from devs;

SELECT * FROM example_view

Now we have an alternate solution of CREATE VIEW if not exists is given below.

DO
$do$
BEGIN
   IF EXISTS (SELECT FROM pg_class WHERE relname = 'example_view') THEN
      RAISE NOTICE 'View already exists';  -- optional
   ELSE
     Execute 'CREATE VIEW example_view AS SELECT devs_name,devs_technology FROM devs;';
   END IF;
END
$do$;

If we run the above command, then it will raise an error View already exists because the first view is checked using the IF EXISTS statement, where pg_class is a system catalog that contains all the views or tables in the Postgresql database. Through the relname attribute, we can access the name of all views or tables.

If the IF EXISTS statement is True then view already exists, otherwise, it will jump on the ELSE statement to create a new view using the ‘CREATE VIEW example_view AS SELECT‘ query.

Read PostgreSQL TO_TIMESTAMP function

Postgresql create view from multiple tables

In Postgresql, we can create a new view from multiple tables using the join clause, and we will two tables named dev_teams, devs that we created in the introduction of this tutorial.

Let’s create a view name multi_view.

CREATE VIEW multi_view AS (SELECT dt.team_department,ds.devs_name FROM devs ds JOIN dev_teams dt on dt.id=ds.devs_team_id);

SELECT * FROM multi_view;

In the above code, First, we have joined the two tables named devs and dev_teams on their id, devs_team_id columns, then after joining the tables we are selecting the dt.team_dapartment column from the dev_teams as dt table, and ds.devs_name column from devs as ds table for creating view name multi_view.

The multi_view contains information of columns team_department and devs_name, which we can see in the below picture that is the output of the above code.

• PostgreSQL DATE Functions with Examples

Postgresql create view from another database and multiple database

In Postgresql, we can create a view from another database with the help of “dblink” that executes the query in a remote database.

Syntax:

dblink(text connname, text sql [, bool fail_on_error]) returns setof record

dblink(text connstr, text sql [, bool fail_on_error]) returns setof record

dblink(text sql [, bool fail_on_error]) returns setof record

The following is the meaning of the above argument with the dblink function.

• connname: It is the name of the connection to use.
• connstr: It is a connection info string.

SQL: It is the SQL query that we wish to execute in the remote database.

fail_on_error: It is the error, that is if true then an error thrown on the remote side of the connection causes an error to also be thrown locally. If it is false, then the remote error is locally reported as a NOTICE, and the function returns no rows.

Let’s understand with an example, for this example, we will connect to a local database that also applies to the remote databases.

Create two separate databases in the Postgresql server running locally using statement.

CREATE DATABASE person_database_one;

CREATE DATABASE person_database_two;

Create a table and insert the following records in person_database_one.

\c person_database_one -- connect to database

CREATE TABLE person(id int,name varchar(20),gender varchar(10));


INSERT INTO person(id,name,gender)VALUES(1,'Decker','Male'),
(2,'Lucifer','Male'),(3,'Dan','Male');

We are going to SELECT person_database_one data from person_database_two, so we have to execute all below scripts on person_database_two to configure the DbLink extension for cross-database queries.

Make a connection with person_database_two using the below command.

\c person_database_two

Now install the dblink extension.

CREATE EXTENSION dblink;

Let’s verify the installed dblink system tables.

SELECT pg_namespace.nspname, pg_proc.proname 
FROM pg_proc, pg_namespace 
WHERE pg_proc.pronamespace=pg_namespace.oid 
   AND pg_proc.proname LIKE '%dblink%';

Check the connection with person_database_one.

SELECT dblink_connect('host=localhost user=postgres password=12345 dbname=person_database_one');

Make foreign data wrapper and the server for the global authentication, after that we can use this server object for cross-database queries.

CREATE FOREIGN DATA WRAPPER postgre VALIDATOR postgresql_fdw_validator;

CREATE SERVER demod_postgre FOREIGN DATA WRAPPER postgre OPTIONS (hostaddr '127.0.0.1', dbname 'person_database_one');

Mapping of user with server.

CREATE USER MAPPING FOR postgres SERVER demod_postgre OPTIONS (user 'postgres', password '12345');

Check the created server is ready or not by connecting to it.

SELECT dblink_connect('demod_postgre');

From the above output, we have successfully connected to a server named demod_postgre.

Let’s create a view named another_view from another database.

CREATE VIEW another_view AS SELECT * FROM dblink ('demod_postgre','CREATE VIEW another_view AS SELECT id,name,gender FROM person') 
AS DATA(id INT,name VARCHAR(20),gender VARCHAR(10));

Read PostgreSQL ADD COLUMN + 17 Examples

Postgresql create view with join

In Postgresql, JOIN can be used to create a new view or a new view can be created using joining two tables, Actually, when we don’t get information from one table we try to join or combine two tables to get information, for that we use JOIN in our queries.

Let’s understand through example by creating a new view named join_view.

CREATE VIEW join_view AS (SELECT * FROM devs ds JOIN dev_teams dt on dt.id=ds.devs_team_id);

SELECT * FROM join_view;

From the above output, we have created a new view join_view that contains all records of both tables. we got these records by combining two tables devs and dev_teams on the columns id and devs_team_id respectively.

Instead of the JOIN clause, we can also use Left and Right or other JOIN.

Read PostgreSQL TO_NUMBER() function

Postgresql create view with calculated column

In Postgresql, the view can be created from a table that contains calculated columns, calculated column data is generated on the fly or automatically as data inserted into the table.

Let’ consider this example by running the below code.

CREATE TABLE tbl (
  integer1    int
, integer2    int
, multiplication bigint GENERATED ALWAYS AS (integer1 * integer2) STORED
);

In the above code, we are creating a new table tbl that contains three columns interger1, integer2, multiplication, and multiplication column is calculated column whose value calculated based on integer1 and integer2 columns.

Let’s insert some records and see the value of calculated columns.

INSERT INTO tbl(integer1,integer2)VALUES(2,3);
INSERT INTO tbl(integer1,integer2)VALUES(7,9);
INSERT INTO tbl(integer1,integer2)VALUES(6,3);
INSERT INTO tbl(integer1,integer2)VALUES(2,14);

SELECT * FROM tbl;

In the above output, we can see the multiplication column values calculated automatically.

Create a view from this table.

CREATE VIEW cal_view AS (SELECT * FROM tbl);

In the above code, we are creating view cal_view that contains all the records or information from the tbl table that we created above.

Read Postgresql row_number

Postgresql create view from another schema

In Postgresql, the view can be created from a schema that can access the table, the schema is a namespace that consists of named database objects such as tables, views, indexes, data types, functions, etc.

Create a schema named table_schema and also create a table named tbl_sch using the below command.

CREATE SCHEMA table_schema;

CREATE TABLE table_schema.tbl_sch(name VARCHAR);

Insert the following data into the table.

INSERT INTO table_schema.tbl_sch(name)VALUES('Trixie');
INSERT INTO table_schema.tbl_sch(name)VALUES('Dan');
INSERT INTO table_schema.tbl_sch(name)VALUES('Eve');

Create a view named schema_view from the above schema by accessing the table.

CREATE VIEW schema_view AS (SELECT * FROM table_schema.tbl_sch);

SELECT * FROM schema_view;

In the above output, we have created a view name schema_view using schema table_schema.

Read Postgresql auto increment

Postgresql create view union all

In Postgresql, The View can be created using UNION ALL operators, UNION ALL are used to combine the result set from more than one SELECT statement into a single result set.

Let’s create a view using union.

CREATE VIEW union_view AS (SELECT team_department FROM dev_teams UNION ALL SELECT devs_position FROM devs);

SELECT * FROM union_view;

From the above output, we have created a view union_view using UNION ALL with two SELECT statements that show results from both tables in a single column.

Remember, for using UNION ALL statements must contain the same number of columns.

Read Postgresql current_timestamp

Postgresql create view order by and group by

In Postgresql, we can also run the ‘order by’ clause on views like base tables but first, let’s add a new column named devs_salary to the devs table that we created above in the introduction section.

ALTER TABLE devs ADD COLUMN devs_salary INTEGER;

Insert some data for the devs_salary column.

UPDATE devs SET devs_salary = 90000
WHERE devs_technology = 'Java';

UPDATE devs SET devs_salary = 25000
WHERE devs_technology = 'Maven';

UPDATE devs SET devs_salary = 50000
WHERE devs_technology = 'Angular';

UPDATE devs SET devs_salary = 80000
WHERE devs_technology = 'Hibernate';

UPDATE devs SET devs_salary = 40000
WHERE devs_technology = 'Digital Marketing';

For ORDER BY clause:

Now, create a view named og_view.

CREATE VIEW og_view AS ( SELECT devs_position,devs_salary
			FROM devs ORDER BY devs_salary desc);
			
SELECT * FROM og_view;

In the above code, First, we have ordered by devs_salary according to devs_position column in desc, which means devs_salary is order in descending order or from high salary to low salary.

Note: We can drop the view using the below syntax.

DROP VIEW view_name;

For GROUP BY clause:

Again create a view named og_view.

CREATE VIEW og_view AS ( SELECT devs_position,max(devs_salary)
		 FROM devs GROUP BY devs_position,devs_salary);
			
SELECT * FROM og_view;

In the above output, we are grouping the columns by devs_position and devs_salary and created a view named og_view that contains these records.

Read Postgresql listen_addresses

Postgresql create view based on another view

Let us see, how to create a view from another existing view in PostgreSQL, let’s understand with the help of an example.

Create a new view using the below code.

CREATE VIEW first_view AS (SELECT devs_name, devs_position, devs_technology FROM devs);

SELECT * FROM first_view;

In the above code, we are creating a view named first_view that contains information of columns devs_name, devs_position, devs_technology from table devs.

The output of the above code is given below in the picture.

Now, we want to create another view name second_view from the existing view named first_view.

Let’s create that.

CREATE VIEW second_view AS (SELECT devs_name, devs_position FROM first_view);

After running the above code, we have created a view named second_view that has information from columns devs_name, devs_position of logical table first_view.

The output of the above code is given below.

Related Posts:

• Postgresql create user with password
• How to set user password in PostgreSQL
• Postgresql create database
• Postgresql ilike case insensitive
• How to migrate from MySQL to Postgres
• Postgresql group_concat
• PostgreSQL Loop Examples

So in this tutorial, we have learned about “Postgresql create view” and we have covered the following topics.

• Postgresql create view with parameters
• Postgresql create view if not exists
• Postgresql create view from multiple tables
• Postgresql create view from another database
• Postgresql create view with join
• Postgresql create view with calculated column
• Postgresql create view from another schema
• Postgresql create view union all
• Postgresql create view order by and group by
• Postgresql create view based on another view