create view in postgresql

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.


How to Create View in PostgreSQL

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

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( 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);
  SELECT product_sales.product,    +
     sum( AS sum+
    FROM product_sales             +
   GROUP BY product_sales.product;


Bonus Read : How to Get Row Number in PostgreSQL


How to Drop Views in PostgreSQL

You can easily drop PostgreSQL views using DROP VIEW statement.

postgresql# DROP VIEW view_name;

Here’s the SQL query to drop view in PostgreSQL

postgresql# DROP VIEW sales_summary;


Hopefully, you can easily create view in PostgreSQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!