How to Create View in PostgreSQL

PostgreSQL views allow you to easily store queries and call them later. SQL views are very useful for saving queries that you need to run frequently. Here’s how to create view in PostgreSQL using PostgreSQL create view statement.

Table of Contents

How to Create View in PostgreSQL

Here are the steps to create view in PostgreSQL. Here’s the syntax of PostgreSQL create view statement

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

In the above SQL query, view_name is the name of SQL view where you want to store your SQL query. TEMP/TEMPORARY is an optional keyword to create temporary views that are dropped at the end of your current session.

After AS keyword, you can enter your entire SQL query that you want to save as view.

Here’s an example to create view in PostgreSQL. Let’s say you have the following table product_sales.

postgres=# select * from product_sales;

 product | order_date | sale
---------+------------+------
 A       | 2020-05-01 |  250
 B       | 2020-05-01 |  350
 C       | 2020-05-01 | 1250
 A       | 2020-05-02 |  450
 B       | 2020-05-02 |  650
 C       | 2020-05-02 | 1050
 A       | 2020-05-03 |  150
 B       | 2020-05-03 |  250
 C       | 2020-05-03 | 1850

Let’s say you want to create SQL view from query that calculates total sales per product. Here’s the SQL query to create PostgreSQL view.

postgres=# create view sales_summary as
           select product, sum(sale)
           from product_sales
           group by product;

postgres=# select * from sales_summary;
 product | sum
---------+------
 B       | 1250
 C       | 4150
 A       |  850

Bonus Read : How to Get First Row Per Group in PostgreSQL

PostgreSQL Create View From Multiple Tables

You can also create view in PostgreSQL from multiple tables using the same approach. Just replace the SELECT query in CREATE VIEW statement to fetch data from multiple tables.

Here’s an example of PostgreSQL CREATE VIEW from multiple tables.

postgres=# create view multi_table as
select product,sale,order_date, order_id
from product_sales,orders
where product_sales.order_id=orders.order_id;

Bonus Read : How to Fill Missing Dates in PostgreSQL

How to Show View definition in PostgreSQL

You can easily see view definition in PostgreSQL using \d+ command. Here’s the SQL query to show view definition

postgres=# \d+ sales_summary;
                      View "public.sales_summary"
 Column  |          Type          | Modifiers | Storage  | Description
---------+------------------------+-----------+----------+-------------
 product | character varying(255) |           | extended |
 sum     | bigint                 |           | plain    |
View definition:
 SELECT product_sales.product,
    sum(product_sales.sale) AS sum
   FROM product_sales
  GROUP BY product_sales.product;

Bonus Read : How to Calculate Percentile in PostgreSQL

You can also do the same thing using pg_get_viewdef function

postgres=# select pg_get_viewdef('sales_summary', true);
           pg_get_viewdef
------------------------------------
  SELECT product_sales.product,    +
     sum(product_sales.sale) AS sum+
    FROM product_sales             +
   GROUP BY product_sales.product;

Bonus Read : How to Get Row Number in PostgreSQL