CREATE VIEW interp AS select DISTINCT itemkey from songs;

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:

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

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