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

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