how can I edit data in a view? Follow
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
Please sign in to leave a comment.
Views cannot be edited in any case.
I just showed in the example that they can!
What I see:
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.
Yes, I agree with you. Please comment and follow the ticket: https://youtrack.jetbrains.com/issue/DBE-2516