Repository navigation
Inconsistency with View Code editor vs Query #9720
Description
Describe the bug
Trying to modify the view in the Code Editor and the editor will not allow the view update to happen, however placing the exact same code in a query window will allow the view to be updated.
To Reproduce
Old View:
COALESCE((pr.sell_price - pr.base_price) / pr.sell_price * pr.quantity * 100::numeric, 0::numeric) AS extended_margin
New View:
COALESCE((pr.sell_price - pr.base_price)::numeric / NULLIF(pr.sell_price::numeric,0) * pr.quantity * 100::numeric, 0::numeric)::numeric AS extended_margin
I thought it was perhaps a data type issue, however PG is reporting the same types:
SELECT
column_name,
data_type,
numeric_precision,
numeric_scale
FROM information_schema.columns
WHERE table_schema = 'procurement'
AND table_name = 'view_products'
AND column_name = 'extended_margin';
SELECT pg_typeof(
COALESCE(
(pr.sell_price - pr.base_price)::numeric
/ NULLIF(pr.sell_price::numeric, 0)
* pr.quantity
* 100::numeric,
0::numeric
)::numeric
)
FROM schema.products pr
LIMIT 1;
Also the column is the last column on the view.
Expected behavior
The View editor would allow the change just the same as the 'CREATE OR REPLACE VIEW' used in the query editor would.
Error message
Desktop (please complete the following information):
Additional context
Add any other context about the problem here.
Activity
dpage commentedon Oct 15, 2026
I've looked into this. The view dialog decides whether it must drop and re-create the view by comparing the text of the definition before the first
FROM(in_get_definition_datainviews/__init__.py, and invalidate()inview.ui.jsfor the warning). Any edit to a column's expression changes that text, so it falls back to DROP + CREATE even when the column names, types and order are unchanged, which is exactly the case whereCREATE OR REPLACE VIEWworks (as it does in the Query Tool); the DROP then fails because other objects depend on the view.A text comparison can't tell "the expression changed" from "the columns changed", so the plan is to ask the server instead: when generating the SQL, prepare the new query (parse and analyse only, no execution) and describe its result columns, then compare their names and types with the view's current columns. DROP + CREATE is only needed when an existing column is renamed, retyped, reordered or removed; adding columns at the end, or changing expressions, can use
CREATE OR REPLACE VIEW. The frontend warning will come from that server-side check rather than from its own text comparison.This builds on the fix for #9838 (#10591), which touches the same comparison.
spageRDG commentedon Oct 15, 2026
Thanks for the explanation and the fix Dave!