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

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

From: Peter Schaefer <schaefer(at)cys(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
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-14 07:39:25
Message-ID: 380588AD.7364C8E@cys.de (view raw or flat)
Thread:
Lists: pgsql-bugs
Tom Lane wrote:
> 
> 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.

I'm using postgresql-6.5.2.tar.gz
The above line should read 
codiak=> CREATE TABLE interpret ( id int2, name varchar() );
codiak=> CREATE VIEW interp AS select DISTINCT ON id id from interpret;
so there is no difference to the development version.


> 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.)

Ok, I do not get the error message: 
ERROR:  DISTINCT not supported in views
It is ok for me that views don't support select DISTINCT,
as long as they report it in an error message.
I was just wondering whether select DISTINCT works at all.
 
> 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

Well, I would need a statement that deletes duplicates from a database,
but so far I haven't worked out how to use the 'AS' since such a statement needs
to refer to [two different unique keys in] the same database twice.
The statement I would want looks like:

SELECT * FROM interpret WHERE NOT EXISTS ( SELECT id as id2 FROM interpret WHERE id2<id2);

I think I can work around it by using a view to alias the table fields,
which is what I will try when it is urgent again.

codiak=> CREATE VIEW interp AS select * from interpret;
codiak=> SELECT * FROM interpret WHERE NOT EXISTS ( SELECT id FROM interp WHERE interp.id<interpret.id );

I don't know, maybe even this would work, though it is not efficient:

CREATE VIEW interdistinct AS SELECT * FROM interpret WHERE NOT EXISTS ( SELECT id FROM interp WHERE interp.id<interpret.id );

Have a nice day,
-- 
Peter Schäfer - mailto:schaefer(at)cys(dot)de, schaefer(at)dfu(dot)de
Motto of the 3D Designer: "I am a meshed potato, I can do the twist".

In response to

pgsql-bugs by date

Next:From: CobDate: 1999-10-14 08:40:09
Subject: lobj bug
Previous:From: Luciano Santos CardosoDate: 1999-10-13 20:54:01
Subject: I have problems in Installation and Configuration of Postgres!!!

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