CREATE OR REPLACE VIEW to Update Views

1. Introduction

Some databases will not allow you to remove a column or add a column from a view. Rather, you would have to use the CREATE OR REPLACE VIEW command to make changes to a view. It is important to note that if a view already exists, the columns must be named the same, with the same data types and in the same order that it was created. With PostgreSQL, the database will allow you to add additional columns at the end of the list.

2. Example Change To View

For example, let's create a view that has the invoice_id, total, and first and last name of the customer:

CREATE VIEW invoice_information
AS
SELECT invoice_id, total, first_name, last_name
FROM invoice 
INNER JOIN customer ON invoice.customer_id = customer.customer_id;

To query the view, we can run the following SELECT statement:

SELECT * 
FROM invoice_information;

If we wanted to have the employee’s first and last name instead of the customer’s first and last name, we could drop the view and then recreate it. However, if there were objects that depend on this view, we would have to drop those as well. Using the CREATE OR REPLACE VIEW allows us to simply recreate it without having to get rid of the dependent objects.

Let us make the change in the view:

CREATE OR REPLACE VIEW invoice_information
AS
SELECT invoice_id, total, employee.first_name, employee.last_name
FROM invoice 
INNER JOIN customer ON invoice.customer_id = customer.customer_id
INNER JOIN employee ON customer.support_rep_id = employee.employee_id;

Even though the names of the columns are the same and are in the same order, we will get this error:

This is because the first_name in the employee table is 20 characters, so the data size does not match. In this case, we could resize the employee table’s first_name column to 40 characters, as well as the last_name:

ALTER TABLE employee
ALTER COLUMN first_name TYPE VARCHAR (40);
ALTER TABLE employee
ALTER COLUMN last_name TYPE VARCHAR (40);

Now if we attempt to make the modification again:

CREATE OR REPLACE VIEW invoice_information
AS
SELECT invoice_id, total, employee.first_name, employee.last_name
FROM invoice 
INNER JOIN customer ON invoice.customer_id = customer.customer_id
INNER JOIN employee ON customer.support_rep_id = employee.employee_id;

We are successful, as the data type, column names, and order all match:

SELECT * 
FROM invoice_information;

Since the data has changed, we may want to also change the name of the view. We can use the ALTER VIEW statement to rename it:

ALTER VIEW invoice_information RENAME TO invoice_employee_info;

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.