From 8b5c0e3db182809c21694f313333f260f2a19258 Mon Sep 17 00:00:00 2001 From: Dean Rasheed Date: Mon, 27 Oct 2025 11:09:35 +0000 Subject: [PATCH v3] doc: Improve the "Policies Applied by Command Type" table. This table was missing MERGE ... THEN DELETE and some of the policies applied during INSERT ... ON CONFLICT and MERGE. Fix that, and try to improve readability by listing the various MERGE cases separately, rather than together with INSERT/UPDATE/DELETE. In addition, mention COPY ... TO along with SELECT, since it behaves in the same way. In addition, correct the paragraph above the table to note that an INSERT ... ON CONFLICT DO NOTHING/UPDATE checks all new rows against the INSERT policy expressions, regardless of whether the rows end up being inserted. Also, in the description of ALL policies, clarify that an ALL policy applied to an INSERT will use the policy's USING expression to check new rows, if it lacks a WITH CHECK expression. Author: Dean Rasheed Reviewed-by: Viktor Holmberg Reviewed-by: Jian He Discussion: https://postgr.es/m/CAEZATCWqnfeChjK=n1V_dYZT4rt4mnq+ybf9c0qXDYTVMsy8pg@mail.gmail.com --- doc/src/sgml/ref/create_policy.sgml | 76 +++++++++++++++++++++++------ 1 file changed, 61 insertions(+), 15 deletions(-) diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index e76c342d3da..13adb5ecae8 100644 --- a/doc/src/sgml/ref/create_policy.sgml +++ b/doc/src/sgml/ref/create_policy.sgml @@ -251,7 +251,9 @@ CREATE POLICY name ON INSERT or UPDATE command attempts to add rows to the table that do not pass the ALL - policy's WITH CHECK expression, the entire + policy's WITH CHECK expression (or its + USING expression, if it does not have a + WITH CHECK expression), the entire command will be aborted. @@ -293,9 +295,9 @@ CREATE POLICY name ON Note that INSERT with ON CONFLICT DO - UPDATE checks INSERT policies' - WITH CHECK expressions only for rows appended - to the relation by the INSERT path. + NOTHING/UPDATE checks INSERT policies' + WITH CHECK expressions for all rows, regardless + of whether they end up being inserted. @@ -424,7 +426,7 @@ CREATE POLICY name ON - SELECT + SELECT / COPY ... TO Existing row — — @@ -440,15 +442,7 @@ CREATE POLICY name ON — - INSERT / MERGE ... THEN INSERT - — - New row - — - — - — - - - INSERT ... RETURNING + INSERT New row @@ -464,7 +458,7 @@ CREATE POLICY name ON — - UPDATE / MERGE ... THEN UPDATE + UPDATE Existing & new rows @@ -483,14 +477,66 @@ CREATE POLICY name ON — Existing row + + INSERT ... ON CONFLICT + New row + New row + — + — + — + ON CONFLICT DO UPDATE + + Existing & new rows + + New row of the auxiliary UPDATE command, which + might be different from the new row of the original + INSERT command. + + + + — + Existing row + + New row + + — + + + MERGE + All candidate source & target rows + — + — + — + — + + + MERGE ... THEN INSERT + + New row + + New row + — + — + — + + + MERGE ... THEN UPDATE Existing & new rows — Existing row New row — + + MERGE ... THEN DELETE + Existing row + — + — + — + Existing row + -- 2.51.0