[SQL] Deleting rows from a table not contained in another table
I want to reimplement DELETE FROM foo; INSERT INTO foo SELECT * FROM bar; in a way which does not touch rows which are not modified (mainly to avoid locking issues). I've come up with this: DELETE FROM foo WHERE NOT EXISTS (SELECT * FROM bar WHERE foo.* IS NOT DISTINCT FROM bar.*); INSERT INTO foo SELECT * FROM bar EXCEPT SELECT * FROM foo; The problem is that the plan for the DELETE doesn't look pretty at all: QUERY PLAN --- Nested Loop Anti Join (cost=313.36..181568.96 rows=1 width=6) Join Filter: (NOT (foo.* IS DISTINCT FROM bar.*)) -> Seq Scan on foo (cost=0.00..293.05 rows=20305 width=38) -> Materialize (cost=313.36..516.40 rows=20305 width=32) -> Seq Scan on bar (cost=0.00..293.05 rows=20305 width=32) (5 rows) Is there some way to turn this into a merge join, short of introducing primary keys and using them to guide the join operation? -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] an aggregate to return max() - 1 value?
Hi, With builtin aggregates is it possible to return the value just before max(col)? Thanks, -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] an aggregate to return max() - 1 value?
'select max(col) where col < max(col)' should work but you have to do 'where col < (select max(col) ' On 03/04/2010 01:09 PM, Louis-David Mitterrand wrote: Hi, With builtin aggregates is it possible to return the value just before max(col)? Thanks, -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] an aggregate to return max() - 1 value?
Louis-David Mitterrand wrote: > Hi, > > With builtin aggregates is it possible to return the value just before > max(col)? > > Thanks, > Mmmm what about select max(col) from table where col not in (select max(col) from table; ? Looks like a double table reading, but it works. Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] an aggregate to return max() - 1 value?
SELECT col FROM tab ORDER BY col DESC OFFSET 1 LIMIT 1 In 8.4 OLAP window functions provide more standard and flexibility method but in this case it wouldn't perform as well: postgres=# select i from (select i, rank() over (order by i desc) as r from i) as x where r = 2; i 99 (1 row) postgres=# select i from (select i, dense_rank() over (order by i desc) as r from i) as x where r = 2; i 99 (1 row) -- greg -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] an aggregate to return max() - 1 value?
Hello select min(x) from (select x from data order by x desc limit 2) s; Pavel 2010/3/4 Louis-David Mitterrand : > Hi, > > With builtin aggregates is it possible to return the value just before > max(col)? > > Thanks, > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] PostgreSQL Active-Active Configuration
Hi everyone, I am looking for some Master-Master replication solutions for PostgreSQL database. Please let me know if you are using one and if you are happy with the performance. Thanks -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
