Update View with Postgres SQL

Introduction

Sometimes you have a type of query that is complex in a way where you would like to package it, name it, and call it from other queries as if it is a table. This is similar but not the same as Stored Procedures. Views allow us to accomplish that in a fairly easy manner. In this article, we will learn to create, use, and update a view in Postgres SQL. This tutorial is structured as follows:

  • What? What is a View in PostgreSQL and what does it do?
  • How? How do we create, update, and use Views in our database?

Prerequisites

  • Beginner-level understanding of how to build SQL statements in Postgres (or MS SQL Server, Oracle, MySQL, etc.), using the free PG Admin tool or some other relational db administration tool, or by using code or script (compiled or not, object-oriented or not) languages like Python, PHP, Java, C#, ASP.Net, Javascript, VB.Net, Ruby, Node, etc.) that allows a connection to your db as well as a method for sending it PL/SQL commands or requests, to query or make changes to a database.
  • Knowledge of the use of the more basic SQL (PL/SQL) terminology, including table, column, SELECT, FROM, JOIN, and WHERE.

What is a VIEW in PostgreSQL?

The PostgreSQL VIEW is basically a query you can create, name, and call upon as if it is a table. Pretty simple, actually. If you ever played with a “lesser” database called Microsoft Access, you would instantly understand because that system, since version 1.0, has had “saved queries” that are pretty mcuh exactly what a View is for Postgres. Not always, but often, the more normalized your database is, the more use you may find in Views.

How do we create a View?

Syntax

CREATE OR REPLACE
    VIEW view_name
    (
        name_column_1
        , name_column_2
        , etc
    )
    WITH
    (
        name_option [= VALUE] [, ... ]
    )
    AS query;

Let’s take the above example line-by-line in a brief and high level manner:

  • We use CREATE OR REPLACE instead of just CREATE because we don’t want to deal with overwrite errors. It’s that simple. You can choose to use just CREATE in case you want to trap errors and react accordingly because your situation dictates that you need to know if the View already exists. We’re taking the more simple route here to ease learning.
  • view_name is the name you choose to give your View so you can call it from within other queries, etc. Remember, PostgreSQL treats Views as if they are tables.
  • name_column_1, etc. This is the typical structure you would use to specify column names in your source table.
  • WITH is optional. It refers to any optional parameters you want to feed to your view.
  • query is your SELECT or VALUES statement that gives the view its rows and columns.

Important note Your new query must return the same columns that were created by the existing view query. This means the same columns in the exact same order and with the same types, but it may add new columns to the end. If the column names are not given, the query supplies them. The functions or operations generating the output columns may be different.

Let’s use that syntax to create a view of our own. We’ll start with two tables.

technologies table: | id_tech | t_name | id_category | |———|————|————-| | 0 | Oracle | 5 | | 1 | Mongo | 6 | | 2 | MySQL | 5 | | 3 | PostgreSQL | 5 | | 4 | Python | 7 | | 5 | PHP | 7 |

categories table: | id | t_name_category | |—-|—————–| | 5 | RDB | | 6 | NoSQL | | 7 | Language |

Before we go further, please note that in the technologies table in the id_category column, the numbers you see there refer to the id column in our categories table.

This image will help with understanding:

![Postgres normalized tables](https://i.gyazo.com/12cb789acff67f48cab336ce324e402f.png “Postgres normalized tables”)

Normally, in a Universe without Views or Stored Procedures, we would query the data for a human-readable report by using a join like so:

SELECT
    technologies.id_tech
    , technologies.t_name
    , technologies.id_category
    , categories.t_name_category
FROM categories
INNER JOIN technologies ON categories.id = technologies.id_category;

Which gives us the following recordset:

![Postgres view of recordset from joined tables](https://i.gyazo.com/a57f7b3105ea2fb5dc14eac8f2ad1a0f.png “Postgres view of recordset from joined tables”)

Note: Odds are that we won’t need output of “id_category” here, but we left it in to provide additional clarity.

So the next step is to make our query into a View. We’ve already written all the code above and merely need to put it together. Here’s what it looks like when we have finished:

CREATE OR REPLACE VIEW view_technologies AS
SELECT
    technologies.id_tech
    , technologies.t_name
    , technologies.id_category
    , categories.t_name_category
FROM categories
INNER JOIN technologies ON categories.id = technologies.id_category;

It’s that simple! Notice how the only change to our query was to add the following line of code at the top before our SELECT statement?

CREATE OR REPLACE VIEW view_technologies AS

So now you can reference that View by the name view_technologies anywhere you would normally reference a table.

Notes:

  • As discussed some above, since we added OR REPLACE to CREATE VIEW, this serves to update our view with whatever new code you put after that CREATE OR REPLACE VIEW view_technologies AS phrase.
  • Another way to update a view could be if you want to change the name of a view, for example. To do this, you would use the following statement: ALTER VIEW view_technologies RENAME TO technologies_joined.
  • As you just saw above, you don’t have to name your view with the prefix “view_”. That is just the naming convention we use so as to hopefully provide as much clarity as possible.
  • Finally, if you want to completely delete your view, the following command will do the trick: DROP VIEW view_technologies.

Conclusion

In this article we learned about and practiced making a View in Postgres and we looked into why and when this can be a useful and powerful tool for increasing efficiency and ease.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.