Can not update view

Question

I am using PgAdmin 4 with PostgreSQL 12. Below is a simple tabledefinition vocabulary and a view vocabulary_input. When I use the SELECT - statement from the vocabulary_input in the Query-tool screen, I can update and add rows. However, when I choose "View / Edit data -- all rows" from vocabulary_input the view is locked. Why is that?

    CREATE TABLE public.vocabulary
    (
        entry text COLLATE pg_catalog."default" NOT NULL,
        description text COLLATE pg_catalog."default",
        reference text COLLATE pg_catalog."default" NOT NULL,
        CONSTRAINT vocabulary_pkey PRIMARY KEY (entry, reference)
    );


    CREATE OR REPLACE VIEW public.vocabulary_input
        AS
        SELECT vocabulary.entry,
        vocabulary.description,
        vocabulary.reference
        FROM vocabulary
        ORDER BY vocabulary.entry, vocabulary.reference;

Unrelated to your problem, but it's better to not include an `ORDER BY` in the view definition — , May 15 '20 at 20:11
Do you get an error specifically saying the view is locked? If not, how do you draw that inference? Also, did you commit the previously described work before moving on to the next task? — jjanes, May 15 '20 at 20:13
@a_horse_with_no_name. When I create another VIEW without the ORDER clause, it does not change the situation.@jjanes PgAdmin shows a lock on all columns. When I try to change a field it ignores the edit when leaving the edit popup. All in contrast when I do the same in the Query screen. Then: no lock and edits can be saved. — cvz, May 15 '20 at 20:37

score 0 · Answer 1 · answered Jun 18 '20 at 19:57

I have posted this question at the developers site of pgadmin with the question if this is a bug or "expected behavior". This is the answer: "I am rejecting this RM, as in the future we will get rid of View/Edit data. We will enhance the query tool further, given that we can edit in place in the query tool."

So, my conclusion is that's is an interface issue and has nothing to do with SQL. See: https://redmine.postgresql.org/issues/5532

Can not update view

1 Answers1