If you try and edit a view that does not use Check Option and has DISTINCT, the
view will fail to be updated. Here an example to cause the failure.
CREATE TABLE x (y varchar, z int);
CREATE VIEW v AS SELECT DISTINCT ON (y) y, z FROM x ORDER BY y, z DESC;
Right click on the view and select Properties.
Security barrier: Off
Check options: Blank or No
Definition:
Change the ORDER BY to y, z DESC, 1
<SAVE>
Error saving properties: INTERNAL SERVER ERROR
ERROR: WITH CHECK OPTION is supported only on automatically updatable views
HINT: Views containing DISTINCT are not automatically updatable.
Looking at the SQL Tab, I see:
CREATE OR REPLACE VIEW public.v
WITH (check_option=no, security_barrier=false)
AS
SELECT DISTINCT ON (x.y) x.y,
x.z
FROM x
ORDER BY x.y, x.z DESC, 1;
1) According to the Postgres Docs for 9.4.x and 9.6.x no is not listed a valid
option for check_option.
2) It appears that Postgres is checking for the DISCTINCT before seeing the
check_option=no
3) Since no does not appear to be valid per the docs, maybe pgAdmin 4 needs to
exclude the WITH line if check_option is set to no.
Lloyd
Lloyd Albin
Database Administrator
Statistical Center for HIV/AIDS Research and Prevention (SCHARP)
Vaccine and Infectious Disease Division (VIDD)
Fred Hutchinson Cancer Research Center (FHCRC)
lal...@fredhutch.org
[http://www.fredhutch.org/content/dam/public/email-signatures/3/fred_hutch_logo.png]
Fred Hutchinson Cancer Research Center
1100 Fairview Ave. N., Mail Stop E3-129
Seattle, WA 98109
fredhutch.org<http://www.fredhutch.org/>
