How To Create an Updatable View in PostgreSQL

dbtut

6 years ago

In PostgreSQL, we can perform insert, update and delete operations on the relations specified in the view via views.

Restrictions

Of course there are some restrictions. You can find these restrictions below.

• GROUP BY, HAVING, LIMIT, OFFSET, DISTINCT, WITH, UNION, INTERSECT, and EXCEPT statements can not be used in the query in View.
• Window function, set-returning function or any aggregate function can not be used.

A user who has the authority to update on View does not need to be authorized on the table.

Only the owner of the view needs to be able to update the table.

You can use updateable views in situations where you want certain users to update certain columns of certain tables.

Let’s continue by making an example.

I will use the table named table_example that I created in the article titled “How to Create a View in PostgreSQL“.

When we query the table, the following result is returned.

select * from table_example;

1	select * from table_example;

We create an updateable view using the following script.

CREATE VIEW  updateable_view_example AS
SELECT name from table_example
WHERE city='NEWYORK';

CREATE VIEW updateable_view_example AS

SELECT name from table_example

WHERE city='NEWYORK';

We see the results by querying View as follows.

SELECT * FROM updateable_view_example;

1	SELECT * FROM updateable_view_example;

Let’s do an insert operation on the table via View.

INSERT INTO updateable_view_example (name,city)
VALUES('Jasonn Statham','London');

1 2	INSERT INTO updateable_view_example (name,city) VALUES('Jasonn Statham','London');

We will get an error like below. Because we did not define the city column in the view.

column “city” of relation “updateable_view_example” does not exist.

I recreate the View as follows.

CREATE OR REPLACE VIEW updateable_view_example AS
SELECT name,city from table_example
WHERE city='NEWYORK';

CREATE OR REPLACE VIEW updateable_view_example AS

SELECT name,city from table_example

WHERE city='NEWYORK';

Later, when we try to insert again, we will be able to perform the operation without any errors.

But when we query the view with the Select statement, we will not be able to see the record we added.

Because in the filter in the view, the city must be NEWYORK.

But in the record we insert, the city column is LONDON.

If you query the table with SELECT statement, you can see the record you added.

SELECT * FROM table_example;

1	SELECT * FROM table_example;

Let’s end the article by doing an update on the updateable view.

UPDATE updateable_view_example SET city='OLDYORK';

1	UPDATE updateable_view_example SET city='OLDYORK';

You may want to look at the following articles about Views.

“How To Create a View On PostgreSQL“,

“How To Create a Materialized View On PostgreSQL“,

“How To Create a Recursive View On PostgreSQL“,

“How To Create an Updatable View WITH CHECK CONSTRAINT in PostgreSQL”