This query identifies all views against which data modification statements (INSERT, UPDATE, DELETE) can be executed without raising an error. It evaluates the is_insertable_into and is_updatable attributes to detect both "naturally" updatable views (where PostgreSQL automatically maps changes to base tables) and views made updatable via the rewrite rule system (specifically DO INSTEAD rules). Notably, this definition encompasses views defined with DO INSTEAD NOTHING rules; while such views do not physically modify data, they are technically considered updatable because they accept DML statements without failure.
Type
General (Overview of some aspect of the database.)
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
This query belongs to the following collections:
Name
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
This query is classified under the following categories:
Name
Description
Derived tables
Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.