[go: up one dir, main page]

Tag: delete

Waiting for PostgreSQL 19 – Add UPDATE/DELETE FOR PORTION OF

On 1st of April 2026, Peter Eisentraut committed patch:

Add UPDATE/DELETE FOR PORTION OF
 
This is an extension of the UPDATE and DELETE commands to do a
"temporal update/delete" based on a range or multirange column.  The
user can say UPDATE t FOR PORTION OF valid_at FROM '2001-01-01' TO
'2002-01-01' SET ... (or likewise with DELETE) where valid_at is a
range or multirange column.
 
The command is automatically limited to rows overlapping the targeted
portion, and only history within those bounds is changed.  If a row
represents history partly inside and partly outside the bounds, then
the command truncates the row's application time to fit within the
targeted portion, then it inserts one or more "temporal leftovers":
new rows containing all the original values, except with the
application-time column changed to only represent the untouched part
of history.
 
To compute the temporal leftovers that are required, we use the *_minus_multi
set-returning functions defined in 5eed8ce50c.
 
- Added bison support for FOR PORTION OF syntax.  The bounds must be
  constant, so we forbid column references, subqueries, etc. We do
  accept functions like NOW().
- Added logic to executor to insert new rows for the "temporal
  leftover" part of a record touched by a FOR PORTION OF query.
- Documented FOR PORTION OF.
- Added tests.
 
Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Discussion: https://www.postgresql.org/message-id/flat/ec498c3d-5f2b-48ec-b989-5561c8aa2024%40illuminatedcomputing.com

Continue reading Waiting for PostgreSQL 19 – Add UPDATE/DELETE FOR PORTION OF

Waiting for PostgreSQL 17 – Add support for MERGE … WHEN NOT MATCHED BY SOURCE.

On 30th of March 2024, Dean Rasheed committed patch:

Add support for MERGE ... WHEN NOT MATCHED BY SOURCE.
 
This allows MERGE commands to include WHEN NOT MATCHED BY SOURCE
actions, which operate on rows that exist in the target relation, but
not in the data source. These actions can execute UPDATE, DELETE, or
DO NOTHING sub-commands.
 
This is in contrast to already-supported WHEN NOT MATCHED actions,
which operate on rows that exist in the data source, but not in the
target relation. To make this distinction clearer, such actions may
now be written as WHEN NOT MATCHED BY TARGET.
 
Writing WHEN NOT MATCHED without specifying BY SOURCE or BY TARGET is
equivalent to writing WHEN NOT MATCHED BY TARGET.
 
Dean Rasheed, reviewed by Alvaro Herrera, Ted Yu and Vik Fearing.
 
Discussion: https://postgr.es/m/CAEZATCWqnKGc57Y_JanUBHQXNKcXd7r=0R4NEZUVwP+syRkWbA@mail.gmail.com

Continue reading Waiting for PostgreSQL 17 – Add support for MERGE … WHEN NOT MATCHED BY SOURCE.

Waiting for 9.6 – Tsvector editing functions

On 11th of March, Teodor Sigaev committed patch:

Tsvector editing functions
 
Adds several tsvector editting function: convert tsvector to/from text array,
set weight for given lexemes, delete lexeme(s), unnest, filter lexemes
with given weights
 
Author: Stas Kelvich with some editorization by me
Reviewers: Tomas Vondram, Teodor Sigaev

Continue reading Waiting for 9.6 – Tsvector editing functions

Waiting for 9.3 – Support automatically-updatable views.

On 8th of December, Tom Lane committed patch:

Support automatically-updatable views.
 
This patch makes "simple" views automatically updatable, without the need
to create either INSTEAD OF triggers or INSTEAD rules.  "Simple" views
are those classified as updatable according to SQL-92 rules.  The rewriter
transforms INSERT/UPDATE/DELETE commands on such views directly into an
equivalent command on the underlying table, which will generally have
noticeably better performance than is possible with either triggers or
user-written rules.  A view that has INSTEAD OF triggers or INSTEAD rules
continues to operate the same as before.
 
For the moment, security_barrier views are not considered simple.
Also, we do not support WITH CHECK OPTION.  These features may be
added in future.
 
Dean Rasheed, reviewed by Amit Kapila

Continue reading Waiting for 9.3 – Support automatically-updatable views.