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?
Table of Contents
To show what PostgreSQL will do, I created a simple table:
|
1 2 3 4 5 6 7 |
view_demo=# CREATE TABLE t_product ( id serial, name text, price numeric(16, 4) ); CREATE TABLE |
My table has just three simple columns and does not contain anything special. Here is the layout of the table:
|
1 2 3 4 5 6 7 |
view_demo=# d t_product Table 'public.t_product' Column | Type | Collation | Nullable | Default --------+---------------+-----------+----------+--------------------------------------- id | integer | | not null | nextval('t_product_id_seq'::regclass) name | text | | | price | numeric(16,4) | | | |
The first thing to do in order to get our demo going is to create a view:
|
1 2 |
view_demo=# CREATE VIEW v AS SELECT * FROM t_product; CREATE 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
view_demo=# d+ v View 'public.v' Column | Type | Collation | Nullable | Default | Storage | Description --------+---------------+-----------+----------+---------+----------+------------- id | integer | | | | plain | name | text | | | | extended | price | numeric(16,4) | | | | main | View definition: SELECT t_product.id, t_product.name, t_product.price FROM t_product; |
What happens if we simply try to rename the table the view is based on:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
view_demo=# ALTER TABLE t_product RENAME TO t_cool_product; ALTER TABLE view_demo=# d+ v View 'public.v' Column | Type | Collation | Nullable | Default | Storage | Description --------+---------------+-----------+----------+---------+----------+------------- id | integer | | | | plain | name | text | | | | extended | price | numeric(16,4) | | | | main | View definition: SELECT t_cool_product.id, t_cool_product.name, t_cool_product.price FROM t_cool_product; |
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.
|
1 2 3 |
view_demo=# ALTER TABLE t_cool_product RENAME COLUMN price TO produce_price; ALTER TABLE |
Again, the view will not be harmed:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
view_demo=# d+ v View 'public.v' Column | Type | Collation | Nullable | Default | Storage | Description --------+---------------+-----------+----------+---------+----------+------------- id | integer | | | | plain | name | text | | | | extended | price | numeric(16,4) | | | | main | View definition: SELECT t_cool_product.id, t_cool_product.name, t_cool_product.produce_price AS price FROM t_cool_product; |
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.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
view_demo=# d pg_rewrite Table 'pg_catalog.pg_rewrite' Column | Type | Collation | Nullable | Default ------------+--------------+-----------+----------+--------- rulename | name | | not null | ev_class | oid | | not null | ev_type | 'char' | | not null | ev_enabled | 'char' | | not null | is_instead | boolean | | not null | ev_qual | pg_node_tree | | | ev_action | pg_node_tree | | | Indexes: 'pg_rewrite_oid_index' UNIQUE, btree (oid) 'pg_rewrite_rel_rulename_index' UNIQUE, btree (ev_class, rulename) |
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.
|
1 2 3 4 |
view_demo=# ALTER TABLE t_cool_product DROP COLUMN name; ERROR: cannot drop table t_cool_product column name because other objects depend on it DETAIL: view v depends on table t_cool_product column name HINT: Use DROP ... CASCADE to drop the dependent objects too. |
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
+43 (0) 2622 93022-0
office@cybertec.at
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
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.