Thread: CREATE VIEW interp AS select DISTINCT itemkey from songs;

Excuse my weak knowledge of SQL, but this doesn't work the way I expect it to work:

codiak=> CREATE VIEW interp AS select DISTINCT ON id id from interp;
CREATE
codiak=> select id from interp where id=1063;
id
-------
   1063
   1063
   1063
(3 rows)

codiak=> \d interp

Table    = interp
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| id                               | int4                             |     4 |
+----------------------------------+----------------------------------+-------+

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

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