how can I edit data in a view?

Answered

If I have a single-table view, I can update fields that are identical in the original table using, e.g., psql. But DataGrip won't let me edit a view. Is there some hidden flag that will allow that? This is with DG 2017.3.7 with a Postgres 9.6 database

Example:

CREATE SCHEMA s;
CREATE TABLE s.t (a integer, b integer, c integer);
CREATE VIEW s.v AS (SELECT a + b as s, c FROM s.t);

INSERT INTO s.t VALUES (1, 1, 0), (1, 2, 1), (2, 3, 2);

SELECT * FROM s.v;
s  | c
----+---
 2 | 0
 3 | 1
 5 | 2

UPDATE s.v SET c=66 where s=5;

SELECT * FROM s.t;

a | b | c
---+---+---
1 | 1 | 0
1 | 2 | 1
2 | 3 | 5

 

Sure enough, s.t changed. I can even DELETE FROM s.v and everything is deleted from s.t. Why won't datagrip let me do that?

 

Thanks

5 comments
Comment actions Permalink

Views cannot be edited in any case.

0
Comment actions Permalink

I just showed in the example that they can!

0
Comment actions Permalink

There is a lot of cases that allows to INSERT/UPDATE views (at least in Postgres):

Anyway - it's DBMS's business to decide either to allow to mutate view or no, not  DataGrip's. Classic case when tool is taking on too much. In result - more work to develop tool, more headache to users, no benefits.

 

BTW, @Maxim Sobolevskiy if you carefully read the error message from YOUR OWN screenshot, you will see that there is refutation of your words 

Views cannot be edited in any case.

 
0
Comment actions Permalink

Yes, I agree with you. Please comment and follow the ticket: https://youtrack.jetbrains.com/issue/DBE-2516

0

Please sign in to leave a comment.