Thread

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

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-10-13T14:27:22Z

   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
   
   

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

   Peter Schaefer <schaefer@cys.de> — 1999-10-14T07:39:25Z

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