Как удалить view postgresql
In PostgreSQL, a view is nothing but a virtual table that is defined based on a SELECT statement. It enables us to store a SELECT query and use it as a table in our database. Postgres views are useful for organizing and accessing data, but if you have too many, they can take up space and slow down your database. It is therefore recommended that review the Postgres views regularly and delete the unnecessary views.
This write-up will guide you on how to drop a view in Postgres using practical examples. So, let’s start.
How to Drop a View in PostgreSQL?
In Postgres, the DROP VIEW statement allows us to delete one or more views from the database. To do that, use the DROP VIEW statement followed by the view’s name to be deleted:
DROP VIEW view_name;
In place of “view_name”, specify the view to be dropped.
Example: Dropping a Postgres View
The below snippet shows the list of available views:
Suppose we want to drop “emp_view”; for that, we will execute the DROP VIEW statement as follows:
DROP VIEW emp_view;
To verify the view’s deletion, use the “\dv” command:
The selected view, i.e., “emp_view”, has been dropped successfully. Let’s execute the DROP VIEW command one more time to see how it works when the selected view doesn’t exist in the database:
DROP VIEW emp_view;
The output shows that Postgres throws an error “view doesn’t exist”. To avoid this error, you need to use the “IF EXISTS” clause with the DROP VIEW statement:
DROP VIEW IF EXISTS emp_view;
The output snippet proves that this time a notice appeared instead of throwing an error. Let’s use the following code to drop the “sample_view” from the database if it exists:
DROP VIEW IF EXISTS sample_view;
The sample_view has been dropped successfully. From the above examples, we can conclude that the “if exists” option drops the selected view if it exists in the database, and it shows a notice if the targeted view doesn’t exist.
In Postgres, the DROP VIEW statement allows us to delete one or more views from the database. To do that, use the DROP VIEW statement followed by the view’s name to be deleted. Postgres throws an error if the view to be dropped/deleted doesn’t exist in the database. To avoid this error, you need to use the “IF EXISTS” clause with the DROP VIEW statement. This post explained the basic syntax, usage, and working of the DROP VIEW statement using suitable examples.
PostgreSQL DROP VIEW IF EXISTS
Can anyone explain what this means? why the word ‘ IF ‘ instead of a response like: View view_name dropped ?
asked May 2, 2019 at 15:59
russellelbert russellelbert
643 4 4 gold badges 9 9 silver badges 23 23 bronze badges
1 Answer 1
That’s not what PostgreSQL does. Demo:
postgres=# create view view_name as select 1; CREATE VIEW postgres=# drop view if exists view_name; DROP VIEW postgres=# \echo :SERVER_VERSION_NUM 110002 postgres=# drop view if exists view_name; NOTICE: view "view_name" does not exist, skipping DROP VIEW Possibly you’re not using PostgreSQL proper, but rather a fork that has a different syntax for this operation. Check out the result of SELECT version(); .
answered May 2, 2019 at 16:30
Daniel Vérité Daniel Vérité
58.5k 15 15 gold badges 130 130 silver badges 156 156 bronze badges
Thank you @daniel-vérité — It’s a PostgreSQL running on a RDS instance in AWS. The SELECT version outcome is: «PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit»
May 3, 2019 at 17:13
@russellelbert: if you run the commands under psql (with a remote connection to RDS), you should get the same results as in my answer above. I suspect that the View IF dropped message comes from a SQL client that tries to interpret the statement and doesn’t do it well enough.
PostgreSQL VIEW (представление)
В этом учебном пособии вы узнаете, как создавать, обновлять и удалять VIEWS в PostgreSQL с синтаксисом и примерами.
Что такое VIEW в 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 следующим образом:
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:
Postgresql Drop View
PostgreSQL views make it easy to save and run SQL queries repeatedly, without having to write the query again and again. However, if you don’t need a PostgreSQL view, you can drop it from your database. Here’s how to drop view in PostgreSQL, using PostgreSQL DROP VIEW statement.
How to Drop View in PostgreSQL
Here are the steps to drop view in PostgreSQL using PostgreSQL DROP VIEW statement.
Here’s the syntax for DROP VIEW statement.
DROP VIEW [ IF EXISTS ] view_name;In the above statement replace view_name with your view name.
If you drop view that doesn’t exist you will get an error. So you can optionally add IF EXISTS keyword to delete view only if it exists. Here’s an example
postgres=# drop view temp_view; ERROR: view "temp_view" does not exist postgres=# drop view if exists temp_view; NOTICE: view "temp_view" does not exist, skipping DROP VIEW
PostgreSQL DROP VIEW Example
Let’s say you have the following view
postgres=# create view sales_view as select * from sales; CREATE VIEW postgres=# select * from sales; order_date | sale ------------+------ 2020-04-01 | 210 2020-04-02 | 125 2020-04-03 | 150 2020-04-04 | 230 2020-04-05 | 200 2020-04-10 | 220 2020-04-06 | 25 2020-04-07 | 215 2020-04-08 | 300 2020-04-09 | 250
Here’s the SQL query to drop view in PostgreSQL.
postgres=# drop view if exists sales_view; DROP VIEW postgres=# select * from sales_view; ERROR: relation "sales_view" does not exist LINE 1: select * from sales_view;
PostgreSQL DROP VIEW Cascade
If you also want to delete PostgreSQL objects that depend on your view, then you need to use CASCADE keyword in your PostgreSQL DROP VIEW query.
Here’s the syntax
PostgreSQL DROP VIEW view_name CASCADE
In the above query, replace view_name with your view name.
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!