05.2018 / Category: / Tags: | | |

In PostgreSQL, a view is a virtual table based on an SQL statement. It is an abstraction layer, which allows to access the result of a more complex SQL fast an easily. The fields in a view are fields from one or more real tables in the database. The question many people now ask if: If a view is based on a table. What happens if the data structure of the underlying table changes?


To show what PostgreSQL will do, I created a simple table:

My table has just three simple columns and does not contain anything special. Here is the layout of the table:


Making changes to tables and views

The first thing to do in order to get our demo going is to create a view:

The important thing here to see is how PostgreSQL handles the view. In the following listing you can see that the view definition does not contain a “*” anymore. PostgreSQL has silently replaced the “*” with the actual column list. Note that this is an important thing because it will have serious implications:

What happens if we simply try to rename the table the view is based on:

As you can see the view will be changed as well. The reason for that is simple: PostgreSQL does not store the view as string. Instead, it will keep a binary copy of the definition around, which is largely based on object ids. The beauty is that if the name of a table or a column changes, those objects will still have the same object id and therefore there is no problem for the view. The view will not break, become invalid or face deletion.

The same happens when you change the name of a column:

Again, the view will not be harmed:

What is really important and noteworthy here is that the view does not change its output. The columns provided by the view will be the same. In other words: Applications relying on the view won't break just because some other column has changed somewhere.

What PostgreSQL does behind the scenes

Behind the scenes a view is handled by the rewrite system. In the system catalog there is a table called pg_rewrite, which will store a binary representation of the view:

Basically this is an internal thing. However, I decided to show how it works behind the scenes, as it might be interesting to know.

Views and dropping columns

However, in some cases PostgreSQL has to error out. Suppose somebody wants to drop a column, on which a view depends on. In this case PostgreSQL has to error out because it cannot silently delete the column from the view.

In this case, PostgreSQL complains that the view cannot be kept around because columns are missing. You can now decide whether to not drop the column or whether to drop the view along with the column.

For further reading on views in PostgreSQL, see View Permissions and Row-Level Security in PostgreSQL by Laurenz Albe

0 0 votes
Article Rating
Notify of
1 Comment
Newest Most Voted
Inline Feedbacks
View all comments
Högvälborne Efraim Pissmyra
Högvälborne Efraim Pissmyra
5 years ago

While I see the advantage of the way PostgreSQL handles views, it is also one of its most annoying features, because it blocks changes to the tables (and worse, other views) it references. We have banned everyone from creating views in our databases for this reason, with only a few careful exceptions.

One of my top wishes for PostgreSQL is to be able to (optionally) create a view like a raw string that is injected like a subquery in the query at runtime.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram