pgadmin-org / pgadmin-org/pgadmin4

Inconsistency with View Code editor vs Query

Open
#9,720 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Bug
Dominant language
Python
Stars
3.8k
Forks
891
Avg merge
4d 7h
Merged PRs (30d)
8

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';
Image
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;
Image

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

Image

Desktop (please complete the following information):

Image

Additional context

Add any other context about the problem here.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the supplied view-change SQL through pgAdmin's View Code editor, then compare it with the same CREATE OR REPLACE VIEW operation in the query editor. Trace the View Code editor entry point and its update validation, using the reported error and final-column example to identify the discrepancy; done means both paths accept the replacement view consistently.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, python
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.