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

Search Discussions

  • Tom Lane at Oct 13, 1999 at 2:28 pm

    Peter Schaefer 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
  • Peter Schaefer at Oct 14, 1999 at 7:41 am

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

Related Discussions

Discussion Navigation
viewthread | post
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