SQL MERGE

The Documentation says a bit ambiguously: First, the MERGE command performs a join from data_source to the target table producing zero or more candidate change rows. For each candidate change row, the status of MATCHED or NOT MATCHED is set just once What kind of join? It’s source LEFT JOIN target. Consider these two tables: hjp=> create table target(id int, m text); CREATE TABLE Time: 18.459 ms hjp=> insert into target(id) values(1), (2), (3), (4); INSERT 0 4 Time: 5.

Move rows to a different table

with rows_to_move as ( delete from t where ts < '2021-05-24 14:22' returning * ) insert into t_arch (select * from rows_to_move);

PostgreSQL Window Functions

A window clause consists of three optional parts, a partition clause, an order clause and a frame clause. The partition clause is similar to a group by clause, except that it can only contain expressions, no output columns. Like the group by clause, it splits the output into parts which are processed independently. The order clause specifies how rows within each partition are ordered. The frame clause finally determines which rows are included in the window.

Generalized Isolation Level Definitions

title Generalized Isolation Level Definitions author Atul Adya author Barbara Liskov author Patrick O’Neil published Proceedings of the IEEE International Conference on Data url http://pmg.csail.mit.edu/papers/icde00.pdf