Skip site navigation (1) Skip section navigation (2)

Re: [BUGS] CREATE VIEW interp AS select DISTINCT itemkey from songs;

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Peter Schaefer <schaefer(at)cys(dot)de>
Cc: "pgsql-bugs(at)postgreSQL(dot)org" <pgsql-bugs(at)postgreSQL(dot)org>
Subject: Re: [BUGS] CREATE VIEW interp AS select DISTINCT itemkey from songs;
Date: 1999-10-13 14:27:22
Message-ID: 20471.939824842@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Peter Schaefer <schaefer(at)cys(dot)de> writes:
> codiak=> CREATE VIEW interp AS select DISTINCT ON id id from interp;

What version are you using?  The current development sources don't like
the above at all:

regression=> CREATE VIEW interp AS select DISTINCT ON id id from interp;
ERROR:  Relation 'interp' does not exist
regression=> create table interp (id int);
CREATE
regression=> CREATE VIEW interp AS select DISTINCT ON id id from interp;
ERROR:  Relation 'interp' already exists
regression=> CREATE VIEW interp1 AS select DISTINCT ON id id from interp;
ERROR:  DISTINCT not supported in views

The reason for the last point is that DISTINCT requires sorting, and
the current implementation method for views doesn't allow a view to
specify an ordering.  (CREATE VIEW ... SELECT ... ORDER BY doesn't
work either.)

You can work around this to some extent by using GROUP BY:

regression=> CREATE VIEW interp1 AS select id from interp group by id;
CREATE

although I think there may be some restrictions on grouped views too.

			regards, tom lane

Responses

pgsql-bugs by date

Next:From: Luciano Santos CardosoDate: 1999-10-13 20:54:01
Subject: I have problems in Installation and Configuration of Postgres!!!
Previous:From: Peter SchaeferDate: 1999-10-13 08:36:57
Subject: CREATE VIEW interp AS select DISTINCT itemkey from songs;

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group