All updatable views

Query goal:

Find all views through which it is possible to modify data in base tables. Is_insertable_into and is_updatable show as to whether the view is naturally updatable in PostgreSQL or has associated rules that make sure that INSERT/UPDATE/DELETE operations against the view will not cause an error. Please note that the rule could be DO INSTEAD NOTHING rule, i.e., data modification through the view does not cause an error but the data is not actually modified. If the updatability is achieved due to rules, then is_updatable=YES if the view has both UPDATE and DELETE rule that make sure that UPDATE and DELETE operations do not cause an error.

Query type:

General (Overview of some aspect of the database.)

Query license:

MIT License

Data source:

INFORMATION_SCHEMA only

SQL query:

Click on query to copy it

SELECT table_schema AS schema, table_name AS view, is_insertable_into, is_updatable, is_trigger_updatable, is_trigger_deletable, is_trigger_insertable_into,
CASE WHEN (view_definition!~'(JOIN|GROUP BY|HAVING|LIMIT|OFFSET|FETCH FIRST|DISTINCT|WITH|UNION|INTERSECT|EXCEPT)'
AND view_definition!~*'(((array|json|jsonb|json_object|jsonb_object|range|range_intersect|string)_|(json_array|xml))agg|(sum|count|avg|min|max|over)[[:space:]]{0,1}[(])') THEN 'YES' ELSE 'NO' END AS is_the_view_by_default_updatable, check_option,
view_definition~*'WHERE[[:space:]]' AS has_where_clause
FROM Information_schema.views
WHERE (is_insertable_into='YES' 
OR is_updatable='YES'
OR is_trigger_updatable='YES' 
OR is_trigger_deletable='YES'
OR is_trigger_insertable_into='YES') 
AND table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
ORDER BY table_schema, table_name;

Collections where the query belongs to

Collection name	Collection description
Find problems by overview	Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

Collection name

Collection description

Find problems by overview

Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

Categories where the query belongs to

Category name	Category description
Derived tables	Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.

Category name

Category description

Derived tables

Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.