Some databases will not allow you to remove a column or add a column from a view. Rather, we would have to use the CREATE OR REPLACE VIEW command to make updates to 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 was created. With PostgreSQL, the database will allow you to add additional columns at the end of the list.
For example, let us 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 intended to have the employee’s first and last name instead of the customer’s first and last name as we wanted to get the details about the employee, we could drop the view and then create 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 will allow 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:
Due to the first_name in the employee table being 20 characters, the data size does not match. We could in this case 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 now 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. Rather than recreating it, we can use the ALTER VIEW statement to rename the view:
ALTER VIEW invoice_information RENAME TO invoice_employee_info;