FAQ

Tom Lane wrote:

Peter Schaefer <schaefer@cys.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@cys.de, schaefer@dfu.de
Motto of the 3D Designer: "I am a meshed potato, I can do the twist".

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 3 of 3 | next ›
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedOct 13, '99 at 8:38a
activeOct 14, '99 at 7:41a
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Peter Schaefer: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase