Thanks for reporting. *RM created:* https://redmine.postgresql.org/issues/1924
-- Regards, Murtuza Zabuawala EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company On Thu, Nov 3, 2016 at 12:30 AM, Albin, Lloyd P <lal...@scharp.org> wrote: > 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 > > [image: > 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/>* > > >
