ALTER VIEW — change the definition of a view
Synopsis
ALTER VIEW [ IF EXISTS ]name
ALTER [ COLUMN ]column_name
SET DEFAULTexpression
ALTER VIEW [ IF EXISTS ]name
ALTER [ COLUMN ]column_name
DROP DEFAULT ALTER VIEW [ IF EXISTS ]name
OWNER TO {new_owner
| CURRENT_ROLE | CURRENT_USER | SESSION_USER } ALTER VIEW [ IF EXISTS ]name
RENAME [ COLUMN ]column_name
TOnew_column_name
ALTER VIEW [ IF EXISTS ]name
RENAME TOnew_name
ALTER VIEW [ IF EXISTS ]name
SET SCHEMAnew_schema
ALTER VIEW [ IF EXISTS ]name
SET (view_option_name
[=view_option_value
] [, ... ] ) ALTER VIEW [ IF EXISTS ]name
RESET (view_option_name
[, ... ] )
Description
ALTER VIEW
changes various auxiliary properties of a view. (If you want to modify the view’s defining query, use CREATE OR REPLACE VIEW
.)
You must own the view to use ALTER VIEW
. To change a view’s schema, you must also have CREATE
privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE
privilege on the view’s schema. (These restrictions enforce that altering the owner doesn’t do anything you couldn’t do by dropping and recreating the view. However, a superuser can alter ownership of any view anyway.)
Parameters
name
-
The name (optionally schema-qualified) of an existing view.
column_name
-
Name of an existing column.
new_column_name
-
New name for an existing column.
IF EXISTS
-
Do not throw an error if the view does not exist. A notice is issued in this case.
SET
/DROP DEFAULT
-
These forms set or remove the default value for a column. A view column’s default value is substituted into any
INSERT
orUPDATE
command whose target is the view, before applying any rules or triggers for the view. The view’s default will therefore take precedence over any default values from underlying relations. new_owner
-
The user name of the new owner of the view.
new_name
-
The new name for the view.
new_schema
-
The new schema for the view.
SET (
view_option_name
[=view_option_value
] [, ... ] )RESET (
view_option_name
[, ... ] )-
Sets or resets a view option. Currently supported options are:
check_option
(enum
)-
Changes the check option of the view. The value must be
local
orcascaded
. security_barrier
(boolean
)-
Changes the security-barrier property of the view. The value must be a Boolean value, such as
true
orfalse
. security_invoker
(boolean
)-
Changes the security-invoker property of the view. The value must be a Boolean value, such as
true
orfalse
.
Notes
For historical reasons, ALTER TABLE
can be used with views too; but the only variants of ALTER TABLE
that are allowed with views are equivalent to the ones shown above.
Examples
To rename the view foo
to bar
:
ALTER VIEW foo RENAME TO bar;
To attach a default column value to an updatable view:
CREATE TABLE base_table (id int, ts timestamptz); CREATE VIEW a_view AS SELECT * FROM base_table; ALTER VIEW a_view ALTER COLUMN ts SET DEFAULT now(); INSERT INTO base_table(id) VALUES(1); -- ts will receive a NULL INSERT INTO a_view(id) VALUES(2); -- ts will receive the current time
Compatibility
ALTER VIEW
is a PostgreSQL extension of the SQL standard.
ALTER VIEW — change the definition of a view
Synopsis
ALTER VIEW [ IF EXISTS ]name
ALTER [ COLUMN ]column_name
SET DEFAULTexpression
ALTER VIEW [ IF EXISTS ]name
ALTER [ COLUMN ]column_name
DROP DEFAULT ALTER VIEW [ IF EXISTS ]name
OWNER TO {new_owner
| CURRENT_USER | SESSION_USER } ALTER VIEW [ IF EXISTS ]name
RENAME [ COLUMN ]column_name
TOnew_column_name
ALTER VIEW [ IF EXISTS ]name
RENAME TOnew_name
ALTER VIEW [ IF EXISTS ]name
SET SCHEMAnew_schema
ALTER VIEW [ IF EXISTS ]name
SET (view_option_name
[=view_option_value
] [, ... ] ) ALTER VIEW [ IF EXISTS ]name
RESET (view_option_name
[, ... ] )
Description
ALTER VIEW
changes various auxiliary properties of a view. (If you want to modify the view’s defining query, use CREATE OR REPLACE VIEW
.)
You must own the view to use ALTER VIEW
. To change a view’s schema, you must also have CREATE
privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE
privilege on the view’s schema. (These restrictions enforce that altering the owner doesn’t do anything you couldn’t do by dropping and recreating the view. However, a superuser can alter ownership of any view anyway.)
Parameters
name
-
The name (optionally schema-qualified) of an existing view.
column_name
-
Name of an existing column.
new_column_name
-
New name for an existing column.
IF EXISTS
-
Do not throw an error if the view does not exist. A notice is issued in this case.
SET
/DROP DEFAULT
-
These forms set or remove the default value for a column. A view column’s default value is substituted into any
INSERT
orUPDATE
command whose target is the view, before applying any rules or triggers for the view. The view’s default will therefore take precedence over any default values from underlying relations. new_owner
-
The user name of the new owner of the view.
new_name
-
The new name for the view.
new_schema
-
The new schema for the view.
SET (
view_option_name
[=view_option_value
] [, ... ] )RESET (
view_option_name
[, ... ] )-
Sets or resets a view option. Currently supported options are:
check_option
(enum
)-
Changes the check option of the view. The value must be
local
orcascaded
. security_barrier
(boolean
)-
Changes the security-barrier property of the view. The value must be Boolean value, such as
true
orfalse
.
Notes
For historical reasons, ALTER TABLE
can be used with views too; but the only variants of ALTER TABLE
that are allowed with views are equivalent to the ones shown above.
Examples
To rename the view foo
to bar
:
ALTER VIEW foo RENAME TO bar;
To attach a default column value to an updatable view:
CREATE TABLE base_table (id int, ts timestamptz); CREATE VIEW a_view AS SELECT * FROM base_table; ALTER VIEW a_view ALTER COLUMN ts SET DEFAULT now(); INSERT INTO base_table(id) VALUES(1); -- ts will receive a NULL INSERT INTO a_view(id) VALUES(2); -- ts will receive the current time
Compatibility
ALTER VIEW
is a PostgreSQL extension of the SQL standard.
ALTER VIEW — change the definition of a view
Synopsis
ALTER VIEW [ IF EXISTS ]name
ALTER [ COLUMN ]column_name
SET DEFAULTexpression
ALTER VIEW [ IF EXISTS ]name
ALTER [ COLUMN ]column_name
DROP DEFAULT ALTER VIEW [ IF EXISTS ]name
OWNER TO {new_owner
| CURRENT_USER | SESSION_USER } ALTER VIEW [ IF EXISTS ]name
RENAME [ COLUMN ]column_name
TOnew_column_name
ALTER VIEW [ IF EXISTS ]name
RENAME TOnew_name
ALTER VIEW [ IF EXISTS ]name
SET SCHEMAnew_schema
ALTER VIEW [ IF EXISTS ]name
SET (view_option_name
[=view_option_value
] [, ... ] ) ALTER VIEW [ IF EXISTS ]name
RESET (view_option_name
[, ... ] )
Description
ALTER VIEW
changes various auxiliary properties of a view. (If you want to modify the view’s defining query, use CREATE OR REPLACE VIEW
.)
You must own the view to use ALTER VIEW
. To change a view’s schema, you must also have CREATE
privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE
privilege on the view’s schema. (These restrictions enforce that altering the owner doesn’t do anything you couldn’t do by dropping and recreating the view. However, a superuser can alter ownership of any view anyway.)
Parameters
name
-
The name (optionally schema-qualified) of an existing view.
column_name
-
Name of an existing column.
new_column_name
-
New name for an existing column.
IF EXISTS
-
Do not throw an error if the view does not exist. A notice is issued in this case.
SET
/DROP DEFAULT
-
These forms set or remove the default value for a column. A view column’s default value is substituted into any
INSERT
orUPDATE
command whose target is the view, before applying any rules or triggers for the view. The view’s default will therefore take precedence over any default values from underlying relations. new_owner
-
The user name of the new owner of the view.
new_name
-
The new name for the view.
new_schema
-
The new schema for the view.
SET (
view_option_name
[=view_option_value
] [, ... ] )RESET (
view_option_name
[, ... ] )-
Sets or resets a view option. Currently supported options are:
check_option
(enum
)-
Changes the check option of the view. The value must be
local
orcascaded
. security_barrier
(boolean
)-
Changes the security-barrier property of the view. The value must be Boolean value, such as
true
orfalse
.
Notes
For historical reasons, ALTER TABLE
can be used with views too; but the only variants of ALTER TABLE
that are allowed with views are equivalent to the ones shown above.
Examples
To rename the view foo
to bar
:
ALTER VIEW foo RENAME TO bar;
To attach a default column value to an updatable view:
CREATE TABLE base_table (id int, ts timestamptz); CREATE VIEW a_view AS SELECT * FROM base_table; ALTER VIEW a_view ALTER COLUMN ts SET DEFAULT now(); INSERT INTO base_table(id) VALUES(1); -- ts will receive a NULL INSERT INTO a_view(id) VALUES(2); -- ts will receive the current time
Compatibility
ALTER VIEW
is a PostgreSQL extension of the SQL standard.
Summary: in this tutorial, you will learn about views and how to manage views in PostgreSQL.
A view is a database object that is of a stored query. A view can be accessed as a virtual table in PostgreSQL. In other words, a PostgreSQL view is a logical table that represents data of one or more underlying tables through a SELECT statement. Notice that a view does not store data physically except for a materialized view.
A view can be very useful in some cases such as:
- A view helps simplify the complexity of a query because you can query a view, which is based on a complex query, using a simple
SELECT
statement. - Like a table, you can grant permission to users through a view that contains specific data that the users are authorized to see.
- A view provides a consistent layer even the columns of underlying table changes.
Creating PostgreSQL Views
To create a view, we use CREATE VIEW
statement. The simplest syntax of the CREATE VIEW
statement is as follows:
Code language: SQL (Structured Query Language) (sql)
CREATE VIEW view_name AS query;
First, you specify the name of the view after the CREATE VIEW
clause, then you put a query after the AS
keyword. A query can be a simple SELECT
statement or a complex SELECT
statement with joins.
PostgreSQL CREATE VIEW example
For example, in our sample database, we have four tables:
-
customer
– stores all customer data -
address
– stores address of customers -
city
– stores city data -
country
– stores country data
If you want to get a complete customers data, you normally construct a join statement as follows:
Code language: SQL (Structured Query Language) (sql)
SELECT cu.customer_id AS id, cu.first_name || ' ' || cu.last_name AS name, a.address, a.postal_code AS "zip code", a.phone, city.city, country.country, CASE WHEN cu.activebool THEN 'active' ELSE '' END AS notes, cu.store_id AS sid FROM customer cu INNER JOIN address a USING (address_id) INNER JOIN city USING (city_id) INNER JOIN country USING (country_id);
The result of the query is as shown in the screenshot below:
This query is quite complex. However, you can create a view named customer_master
as follows:
Code language: PHP (php)
CREATE VIEW customer_master AS SELECT cu.customer_id AS id, cu.first_name || ' ' || cu.last_name AS name, a.address, a.postal_code AS "zip code", a.phone, city.city, country.country, CASE WHEN cu.activebool THEN 'active' ELSE '' END AS notes, cu.store_id AS sid FROM customer cu INNER JOIN address a USING (address_id) INNER JOIN city USING (city_id) INNER JOIN country USING (country_id);
From now on, whenever you need to get a complete customer data, you just query it from the view by executing the following simple SELECT
statement:
Code language: SQL (Structured Query Language) (sql)
SELECT * FROM customer_master;
This query produces the same result as the complex one with joins above.
Changing PostgreSQL Views
To change the defining query of a view, you use the CREATE VIEW
statement with OR REPLACE
addition as follows:
Code language: SQL (Structured Query Language) (sql)
CREATE OR REPLACE view_name AS query
PostgreSQL does not support removing an existing column in the view, at least up to version 9.4. If you try to do it, you will get an error message: “[Err] ERROR: cannot drop columns from view”. The query must generate the same columns that were generated when the view was created. To be more specific, the new columns must have the same names, same data types, and in the same order as they were created. However, PostgreSQL allows you to append additional columns at the end of the column list.
For example, you can add an email to the customer_master
view as follows:
Code language: PHP (php)
CREATE VIEW customer_master AS SELECT cu.customer_id AS id, cu.first_name || ' ' || cu.last_name AS name, a.address, a.postal_code AS "zip code", a.phone, city.city, country.country, CASE WHEN cu.activebool THEN 'active' ELSE '' END AS notes, cu.store_id AS sid, cu.email FROM customer cu INNER JOIN address a USING (address_id) INNER JOIN city USING (city_id) INNER JOIN country USING (country_id);
Now, if you select data from the customer_master
view, you will see the email
column at the end of the list.
Code language: SQL (Structured Query Language) (sql)
SELECT * FROM customer_master;
To change the definition of a view, you use the ALTER VIEW
statement. For example, you can change the name of the view from customer_master
to customer_info
by using the following statement:
Code language: SQL (Structured Query Language) (sql)
ALTER VIEW customer_master RENAME TO customer_info;
PostgreSQL allows you to set a default value for a column name, change the view’s schema, set or reset options of a view. For detailed information on the altering view’s definition, check it out the PostgreSQL ALTER VIEW statement.
Removing PostgreSQL Views
To remove an existing view in PostgreSQL, you use DROP VIEW
statement as follows:
Code language: SQL (Structured Query Language) (sql)
DROP VIEW [ IF EXISTS ] view_name;
You specify the name of the view that you want to remove after DROP VIEW
clause. Removing a view that does not exist in the database will result in an error. To avoid this, you normally add IF EXISTS
option to the statement to instruct PostgreSQL to remove the view if it exists, otherwise, do nothing.
For example, to remove the customer_info
view that you have created, you execute the following query:
Code language: SQL (Structured Query Language) (sql)
DROP VIEW IF EXISTS customer_info;
The view customer_info
is removed from the database.
In this tutorial, we have shown you how to create, alter, and remove PostgreSQL views.
Was this tutorial helpful ?
PostgreSQL does not allow altering a view (i.e. adding column, changing column orders, adding criterie etc.) if it has dependent objects. This is really getting annoying since you have to write a script to:
- Drop all the dependent objects,
- Alter the view,
- Recreate all the dependent objects back again.
I understand that postgreSQL developers have very reasonable concerns to prevent altering views. But do you guys have any scripts/shot-cuts to do all those manual stuff in a single run?
asked Aug 29, 2010 at 4:54
Adding new columns isn’t a problem, changing datatypes or changing the order of the columns, that’s where you get problems.
-
Don’t change the order, it’s not
that important anyway, just change your query:SELECT a, b FROM view_name;
SELECT b, a FROM view_name;
-
When you have to change a datatype
of a column, you have to check the
depend objects as well. These might
have problems with this new
datatype. Just get the definition of
this object and recreate after the
changes. The information_schema and
pg_catalog help you out. - Make all changes within a single
transaction.
answered Aug 29, 2010 at 8:45
Frank HeikensFrank Heikens
113k24 gold badges137 silver badges132 bronze badges
5
If I place a addtional «drop view xyz; commit;» before the «create or replace view xyz as …» statement, at least in many cases I resolve the blocking problem described above.
answered Aug 14, 2014 at 17:23
Hartmut PfarrHartmut Pfarr
5,2165 gold badges36 silver badges42 bronze badges
1
ALTER VIEW-изменить определение представления
Synopsis
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT ALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ALTER VIEW [ IF EXISTS ] name RENAME [ COLUMN ] column_name TO new_column_name ALTER VIEW [ IF EXISTS ] name RENAME TO new_name ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] ) ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )
Description
ALTER VIEW
изменяет различные вспомогательные свойства представления. (Если вы хотите изменить определяющий запрос представления, используйте CREATE OR REPLACE VIEW
.)
Вы должны владеть представлением, чтобы использовать ALTER VIEW
. Чтобы изменить схему представления, вы также должны иметь привилегию CREATE
для новой схемы. Чтобы изменить владельца, вы также должны быть прямым или косвенным членом новой роли-владельца, и эта роль должна иметь право CREATE
в схеме представления. (Эти ограничения предписывают, что изменение владельца не приводит к тому, что вы не могли бы сделать, отбрасывая и воссоздавая представление. Однако суперпользователь в любом случае может изменить владельца любого представления.)
Parameters
name
-
Название (по желанию-схематическое)существующего вида.
column_name
-
Имя существующего столбца.
new_column_name
-
Новое название для существующей колонки.
IF EXISTS
-
Не выбрасывайте ошибку,если вид не существует.В этом случае выдается уведомление.
-
SET
/DROP DEFAULT
-
Эти формы устанавливают или удаляют значение по умолчанию для столбца. Значение по умолчанию для столбца представления подставляется в любую команду
INSERT
илиUPDATE
, целью которой является представление, перед применением каких-либо правил или триггеров для представления. Следовательно, значение по умолчанию для представления будет иметь приоритет над любыми значениями по умолчанию из базовых отношений. new_owner
-
Имя пользователя нового владельца вида.
new_name
-
Новое название вида.
new_schema
-
Новая схема вида.
-
SET ( view_option_name [= view_option_value] [, ... ] )
RESET ( view_option_name [, ... ] )
-
Устанавливает или сбрасывает опцию просмотра.В настоящее время поддерживаются следующие опции:
-
check_option
(enum
) -
Изменяет параметр проверки вида. Значение должно быть
local
илиcascaded
. -
security_barrier
(boolean
) -
Изменяет свойство барьера безопасности представления. Значение должно быть логическим значением, например
true
илиfalse
. -
security_invoker
(boolean
) -
Изменяет свойство security-invoker представления. Значение должно быть логическим значением, например
true
илиfalse
.
-
Notes
По историческим причинам ALTER TABLE
также может использоваться с представлениями; но единственные варианты ALTER TABLE
, которые разрешены с представлениями, эквивалентны показанным выше.
Examples
Чтобы переименовать представление foo
в bar
:
ALTER VIEW foo RENAME TO bar;
Для прикрепления значения столбца по умолчанию к обновляемому виду:
CREATE TABLE base_table (id int, ts timestamptz); CREATE VIEW a_view AS SELECT * FROM base_table; ALTER VIEW a_view ALTER COLUMN ts SET DEFAULT now(); INSERT INTO base_table(id) VALUES(1); INSERT INTO a_view(id) VALUES(2);
Compatibility
ALTER VIEW
— это расширение PostgreSQL стандарта SQL.
PostgreSQL
15.0
-
ALTER USER
ALTER USER изменить роль базы данных ALTER USER теперь является псевдонимом для ROLE.
-
ИЗМЕНИТЬ ПОЛЬЗОВАТЕЛЬСКОЕ ОТОБРАЖЕНИЕ
ALTER USER MAPPING изменяет определение ALTER USER MAPPING изменяет определение Владелец иностранного сервера может изменять отображения пользователей,которые любые
-
ANALYZE
ANALYZE собирает статистику о базе данных ANALYZE собирает статистику о содержимом таблиц в базе данных и сохраняет результаты pg_statistic system
-
BEGIN
BEGIN запустить блок транзакции BEGIN инициирует блок транзакции,то есть все утверждения после команды будут выполняться поодиночке до явного COMMIT или ROLLBACK
В этом учебном пособии вы узнаете, как создавать, обновлять и удалять VIEWS в PostgreSQL с синтаксисом и примерами.
В PostgreSQL VIEW это не физическая таблица, а скорее виртуальная таблица, созданная запросом joins, соединяющим одну или несколько таблиц.
Создать VIEW
Синтаксис
Синтаксис оператора CREATE VIEW в PostgreSQL:
CREATE [OR REPLACE] VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions];
- OR REPLACE
- Необязательный. Если вы не укажете этот оператор, а VIEW уже существует, оператор CREATE VIEW вернет ошибку.
- view_name
- Имя VIEW, которое вы хотите создать в PostgreSQL.
- WHERE conditions
- Необязательный. Условия, которые должны быть выполнены для включения записей в VIEW.
Пример
Вот пример того, как использовать оператор CREATE VIEW для создания представления в PostgreSQL:
CREATE VIEW current_inventory AS SELECT product_name, quantity FROM products WHERE quantity > 0; |
Этот пример CREATE VIEW создаст виртуальную таблицу на основе результирующего набора оператора SELECT. Теперь вы можете запросить PostgreSQL VIEW следующим образом:
SELECT * FROM current_inventory; |
Обновить VIEW
Вы можете изменить определение VIEW в PostgreSQL, не удаляя его, используя оператор CREATE OR REPLACE VIEW.
Синтаксис
Синтаксис для оператора CREATE OR REPLACE VIEW в PostgreSQL:
CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM table
WHERE conditions;
- view_name
- Название представления, которое вы хотите обновить.
Пример
Вот пример того, как может использоваться оператор CREATE OR REPLACE VIEW в PostgreSQL:
CREATE or REPLACE VIEW current_inventory AS SELECT product_name, quantity, category_name FROM products INNER JOIN categories ON products.category_id = categories.category_id WHERE quantity > 0; |
Этот пример CREATE OR REPLACE VIEW обновил бы определение VIEW с именем current_inventory, не удаляя его.
ВНИМАНИЕ:
Оператор CREATE OR REPLACE VIEW будет работать, если вы добавляете столбцы в представление в конце списка. Тем не менее, это будет ошибка, если вы добавляете новые столбцы в существующие столбцы (то есть: начало или середина существующего списка).
В этом случае не используйте оператор CREATE OR REPLACE VIEW. Лучше удалить VIEW и использовать оператор CREATE VIEW!
Удалить VIEW
После создания VIEW в PostgreSQL вы можете удалить его с помощью оператора Drop VIEW.
Синтаксис
Синтаксис для оператора Drop VIEW в PostgreSQL:
Drop VIEW [IF EXISTS] view_name;
- view_name
- Название представления, которое вы хотите удалить.
- IF EXISTS
- Необязательный. Если вы не укажете этот параметр и VIEW не существует, оператор Drop VIEW вернет ошибку.
Пример
Вот пример того, как использовать оператор Drop VIEW в PostgreSQL:
Drop VIEW current_inventory; |
Этот пример удалит VIEW с именем current_inventory.
Introduction to PostgreSQL Views
In these articles, we will learn about PostgreSQL Views. Views are basically a query with a name; therefore, a view is useful for wrapping a commonly used complex query; we can represent data in the database tables using views named query, we can define views using one or more tables known as base tables, the view is a logical table which represents data from one or many tables.using the SELECT command.
The query’s complexity can be simplified with the help of view as users can query a view using a SELECT command. Like a table, we can add permission to the users using a view that stores data for which we need authorized users only.
How to Create PostgreSQL Views?
Let us see how to create the views:
Syntax:
CREATE [OR REPLACE][TEMP OR TEMPORARY] [RECURSIVE]
VIEW view_name [(column_name [, ...])]
[ WITH (view_options_name [= view_options_value] [, ... ])]
Explanation: If a view with the same name already exists, it is replaced. The view name must be unique. It should not be the same as any other view, sequence, table, foreign table or index in the same schema.
- TEMP / TEMPORARY: If the view is created as a temporary view, then it is automatically removed at the end of the session.
- RECURSIVE: Creates a recursive view.
- name: The name of a view to be
- column_name: The user can define a list of column names of the view. Only defined columns will get considered in query others are
- WITH: ( view_options_name [= view_options_value]…)We can specify optional parameters for a view.
Creating PostgreSQL Views
We can create PostgreSQL views using various ways:
Consider the following tables to understand the PostgreSQL views:
To understand the examples of considering the following ‘student’ and ‘Branch’ table structures
- Student: rollno, firstname, lastname, branch_id, result, joining_date
- Branch: branch_id, branch
1. WHERE clause
Code:
CREATE VIEW student_view
AS SELECT rollno, firstname, lastname, result, joining_date
FROM student
WHERE branch_id = 4;
It will create a view ‘student _view’ taking records (for rollno, firstname, lastname, result, joining_date columns) of the student table if those records contain the value 4 for branch_id column.
Code:
select * from student_view
Output:
2. AND and OR
Code:
CREATE VIEW my_student_view
AS SELECT *
FROM student
WHERE(branch_id = 1 AND result = false)
OR(branch_id = 2 AND firstname='Jacob');
- It will create a view ‘my_student_view’ taking records for all student table columns
- if branch_id is 1, and the result is false.
- or branch_id is 2, and the firstname is ‘Jacob’.
Code:
select * from student_view
Output:
3. GROUP BY
Code:
CREATE VIEW my_student_view
AS SELECT branch_id, count (*)
FROM student
GROUP BY branch_id;
It will create a view ‘my_student_view’ taking all records grouped with respect to branch_id and stored branch_id and several students for each Branch (branch_id) from the student table.
Code:
select * from student_view
Output:
4. ORDER BY
Code:
CREATE VIEW my_student_view
AS SELECT branch_id, count (*)
FROM student
GROUP BY branch_id
order by branch_id;
It will create a view of my_student_view, taking all the records grouped with respect to branch_id and sorted against branch_id and the number of student tables for each department (branch_id) from the student table.
Code:
select * from student_view
Output:
5. BETWEEN and IN
Code:
CREATE VIEW my_student_view AS
SELECT * FROM student
WHERE firstname BETWEEN 'G' AND 'K'
AND rollno IN (101,102,103,105);
It will create a view ‘my_student_view’ taking all the records of employees table; if the firstname column of the student starts with any of the characters from ‘G’ through ‘K’ and rollno are any of the following 101,102,103,105
Code:
select * from student_view
Output:
6. LIKE
Code:
CREATE VIEW my_student_view
AS SELECT *
FROM student
WHERE firstname NOT LIKE 'J%' AND lastname NOT LIKE
'C%';
It will create a view my_student_view taking all the records of the student table. If the student’s firstname does not start with ‘J’ and lastname of the student does not start with ‘C’.
Code:
select * from student_view
Output:
7. Subqueries
Code:
CREATE VIEW my_student_view
AS SELECT rollno,firstname,lastname
FROM student
WHERE branch_id IN(
SELECT branch_id
FROM Branch
WHERE branch_id IN (1,2)
);
It will create a view my_student_view taking all the records of rollno, firstname, lastname of student table. The subquery retrieves those branch_id’s from the Branch table, which branch_id are any of the 1 and 2.
Code:
select * from student_view
Output:
8. JOIN
Code:
CREATE VIEW my_student_view
AS SELECT s.rollno, s.firstname, s.lastname, b.branch
FROM student s, branch b
WHERE s.branch_id = b.branch_id;
It will create a view of my_student_view along with a JOIN statement. The JOIN statement here retrieves rollno, firstname, lastname, from student table and branch_id from branch table if branch_id of student table and that of the branch are the same.
Code:
select * from student_view
Output:
How to Modify PostgreSQL Views?
ALTER VIEW statement modifies the definition of an existing view.
1. ALTER with SET DEFAULT
ALTER VIEW [ IF EXISTS] views_name ALTER [ COLUMN ] column_name SET DEFAULT expression
2. ALTER with DROP
ALTER VIEW [ IF EXISTS] views_name ALTER [ COLUMN ] column_name DROP DEFAULT
3. ALTER with OWNER
ALTER VIEW [ IF EXISTS] views_name OWNER TO new_view_owner
4. ALTER with RENAME
ALTER VIEW [ IF EXISTS] views_name RENAME TO new_view_name
5. ALTER with SET SCHEMA
ALTER VIEW [ IF EXISTS] views_name SET SCHEMA new_view_schema
6. ALTER with SET
ALTER VIEW [ IF EXISTS] views_name SET ( view_options_name [= view_options_value] ... )
7. ALTER with RESET
ALTER VIEW [ IF EXISTS ] views_name RESET ( view_options_name ... )
Explanation:
- views_name: The name of the
- IF EXISTS: Do not throw an error if the view does not exist.
- SET/DROP DEFAULT: Set or remove the default value for a column.
- new_view_owner: Defines view’s new owner’s username.
- new_view_name: The new name for the view.
- new_view_schema: The new schema for the view.
- view_options_name : Defines view options to be set/
- view_options_value : Defines the new value for associated view options.
To rename the view ABC to XYZ.
ALTER VIEW test_view RENAME TO testview;
How to Drop PostgreSQL Views?
- To remove a view, we must use the DROP VIEW statement.
- To drop a view, users must have the DROP privilege for each view.
Syntax:
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[ CASCADE | RESTRICT]
Explanation:
- IF EXISTS: Do not throw an error if the view does not exist.
- view_name: The name (optionally schema-qualified) of the view to remove.
- CASCADE: It will automatically remove all view-dependent objects.
- RESTRICT: It will not allow you to delete view if any dependent object exists.
The following command will remove the view called ‘postgresql_view’:
DROP VIEW 'postgresql_view';
Conclusion
We hope you have understood the concept of PostgreSQL views. And also learned about how to create, modify, and remove PostgreSQL views.
Recommended Articles
This is a guide to PostgreSQL Views. Here we discuss Syntax and also learned about how to create, modify, and remove PostgreSQL views. You can also go through our other related articles to learn more –
- PostgreSQL Data Types
- PostgreSQL WHERE Clause
- Relational Database
- What is PostgreSQL?
- Learn the Examples of PostgreSQL FETCH
- How to Work Sequence in PostgreSQL?
- Browse categories
- Random entry
- Blog
-
PostgreSQL releases
- PostgreSQL 16 (dev)
- PostgreSQL 15 (latest)
- PostgreSQL 14
- PostgreSQL 13
- PostgreSQL 12
- PostgreSQL 11
- PostgreSQL 10 (EOL)
An SQL command for modifying a view
ALTER VIEW
is a DDL command for modifying a view.
ALTER VIEW
was added in PostgreSQL 8.3.
Usage
ALTER VIEW
can be used to modify various properties and attributes of a view. However it cannot be used to change the underlying definition; this can only be done with the CREATE OR REPLACE VIEW ...
syntax.
For historical reasons, ALTER TABLE
can be used as an alternative to ALTER VIEW
for syntax which is valid for both tables and views.
Change history
- PostgreSQL 15
security_invoker
option added (commit 7faa5fc8)
- PostgreSQL 14
ALTER VIEW ... OWNER TO CURRENT_ROLE
syntax added (commit 45b98057)
- PostgreSQL 13
ALTER VIEW ... RENAME COLUMN ...
syntax added (commit 30840c92)
- PostgreSQL 9.4
ALTER VIEW ... OWNER TO { CURRENT_USER | SESSION_USER }
syntax added (commit 31eae602)
- PostgreSQL 9.2
ALTER VIEW IF EXISTS ...
syntax added (commit b8a91d9d)ALTER VIEW ... [ SET | RESET ]
syntax added (commit 0e4611c0)
- PostgreSQL 8.4
- following syntax added (commit a0b012a1):
ALTER VIEW ... ALTER COLUMN ... [ SET | DROP ] DEFAULT
ALTER VIEW ... OWNER TO ...
ALTER VIEW ... SET SCHEMA ...
- following syntax added (commit a0b012a1):
- PostgreSQL 8.3
- added (commit a5589813)
Examples
Basic usage example for ALTER VIEW
:
postgres=# ALTER VIEW bar RENAME TO baz; ALTER VIEW
ALTER TABLE
can be used on views as long as the syntax used is valid for views:
postgres=# ALTER TABLE baz RENAME TO boo; ALTER TABLE postgres=# d boo View "public.boo" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer |
Using ALTER TABLE
syntax not valid for views:
postgres=# ALTER TABLE boo SET TABLESPACE foo; ERROR: ALTER action SET TABLESPACE cannot be performed on relation "boo" DETAIL: This operation is not supported for views.
References
- PostgreSQL documentation: ALTER VIEW