How to delete the not DISTINCT ON entries

O kyrios Christoph Haller egrapse stis Jun 16, 2004 :

Referring to the DISTINCT ON example

SELECT DISTINCT ON (location) location, time, report
FROM weatherReports
ORDER BY location, time DESC;

maybe smth like

delete from weatherReports where (location,time,report) not in
(SELECT DISTINCT ON (location) location, time, report FROM weatherReports
ORDER BY location, time DESC)

Note:
Order by is very important, since it affects which rows are deleted.

How would I delete those entries skipped by the DISTINCT ON expression?

TIA

Regards, Christoph

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
-Achilleus

If you do not have foreign key restrinctions, create a temp table from
the select as:

CREATE TEMP TABLE tmp AS SELECT DISTINCT ON (location) location, time,
report FROM weatherReports ORDER BY location, time DESC;

TRUNCATE weatherReports; INSERT INTO weatherReports SELECT * FROM tmp;

HTH

Achilleus Mantzios wrote:

Hi, i noticed a failure when i pg_dump/reload a database with tsearch2.sql
installed.

I get ERRORS of the type:

dynacom=# SELECT lexize('en_stem','foo bar');
ERROR: cache lookup failed for function 4652394
dynacom=# SELECT lexize('en_stem','foo bar');
ERROR: cache lookup failed for function 4652394
dynacom=# SELECT lexize('en_stem','foo bar');
ERROR: cache lookup failed for function 4652394
dynacom=# SELECT lexize('en_stem','foo bar');
ERROR: cache lookup failed for function 4652394
dynacom=#
dynacom=# SELECT to_tsvector('default','foo bar');
ERROR: cache lookup failed for function 4652424
dynacom=# SELECT to_tsvector('default','foo bar');
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>
!>
!> \q

Always the 1st call in a session to to_tsvector gives:
ERROR: cache lookup failed for function 4652424
and the very next call in the same session causes the postmaster
to SEGV.

This situation is demonstrated in FreeBSD 5.1-RELEASE-p10, pgsql 7.4.1
and in Debian 2.4.18-bf2.4, pgsql 7.4.2.

A workaround is to first load tsearch2.sql script in the newly created
database, reload the dump, and ignore any tsearch2 subsequent ERRORS.

Anyone has a clue?

--
-Achilleus

Achilleus,

we have regprocedure_7.4.patch.gz (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/)

Oleg

On Fri, 25 Jun 2004, Achilleus Mantzios wrote:

Hi, i noticed a failure when i pg_dump/reload a database with tsearch2.sql
installed.

I get ERRORS of the type:

dynacom=# SELECT lexize('en_stem','foo bar');
ERROR: cache lookup failed for function 4652394
dynacom=# SELECT lexize('en_stem','foo bar');
ERROR: cache lookup failed for function 4652394
dynacom=# SELECT lexize('en_stem','foo bar');
ERROR: cache lookup failed for function 4652394
dynacom=# SELECT lexize('en_stem','foo bar');
ERROR: cache lookup failed for function 4652394
dynacom=#
dynacom=# SELECT to_tsvector('default','foo bar');
ERROR: cache lookup failed for function 4652424
dynacom=# SELECT to_tsvector('default','foo bar');
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>
!>
!> \q

Always the 1st call in a session to to_tsvector gives:
ERROR: cache lookup failed for function 4652424
and the very next call in the same session causes the postmaster
to SEGV.

This situation is demonstrated in FreeBSD 5.1-RELEASE-p10, pgsql 7.4.1
and in Debian 2.4.18-bf2.4, pgsql 7.4.2.

A workaround is to first load tsearch2.sql script in the newly created
database, reload the dump, and ignore any tsearch2 subsequent ERRORS.

Anyone has a clue?

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83