Subject: [PATCH] Allow preserving existing values during DBAL upsert
---
Index: src/lib/doctrine-dbal-bulk/src/Flow/Doctrine/Bulk/Dialect/PostgreSQLUpdateOptions.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/lib/doctrine-dbal-bulk/src/Flow/Doctrine/Bulk/Dialect/PostgreSQLUpdateOptions.php b/src/lib/doctrine-dbal-bulk/src/Flow/Doctrine/Bulk/Dialect/PostgreSQLUpdateOptions.php
--- a/src/lib/doctrine-dbal-bulk/src/Flow/Doctrine/Bulk/Dialect/PostgreSQLUpdateOptions.php	(revision 12eda07dc13619a72f7e75c0a4206eb93696c7ed)
+++ b/src/lib/doctrine-dbal-bulk/src/Flow/Doctrine/Bulk/Dialect/PostgreSQLUpdateOptions.php	(date 1767695054871)
@@ -4,7 +4,7 @@
 
 namespace Flow\Doctrine\Bulk\Dialect;
 
-use function Flow\Types\DSL\{type_list, type_string, type_structure};
+use function Flow\Types\DSL\{type_boolean, type_list, type_optional, type_string, type_structure};
 use Flow\Doctrine\Bulk\UpdateOptions;
 
 final readonly class PostgreSQLUpdateOptions implements UpdateOptions
@@ -16,6 +16,7 @@
     public function __construct(
         public array $primaryKeyColumns = [],
         public array $updateColumns = [],
+        public ?bool $preserveExistingValues = null,
     ) {
     }
 
@@ -28,12 +29,14 @@
             optional_elements: [
                 'primary_key_columns' => type_list(type_string()),
                 'update_columns' => type_list(type_string()),
+                'preserve_existing_values' => type_optional(type_boolean()),
             ]
         )->assert($options);
 
         return new self(
             $options['primary_key_columns'] ?? [],
             $options['update_columns'] ?? [],
+            $options['preserve_existing_values'] ?? null,
         );
     }
 
Index: src/lib/doctrine-dbal-bulk/src/Flow/Doctrine/Bulk/Dialect/PostgreSQLInsertOptions.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/lib/doctrine-dbal-bulk/src/Flow/Doctrine/Bulk/Dialect/PostgreSQLInsertOptions.php b/src/lib/doctrine-dbal-bulk/src/Flow/Doctrine/Bulk/Dialect/PostgreSQLInsertOptions.php
--- a/src/lib/doctrine-dbal-bulk/src/Flow/Doctrine/Bulk/Dialect/PostgreSQLInsertOptions.php	(revision 12eda07dc13619a72f7e75c0a4206eb93696c7ed)
+++ b/src/lib/doctrine-dbal-bulk/src/Flow/Doctrine/Bulk/Dialect/PostgreSQLInsertOptions.php	(date 1767695054867)
@@ -18,6 +18,7 @@
         public ?string $constraint = null,
         public array $conflictColumns = [],
         public array $updateColumns = [],
+        public ?bool $preserveExistingValues = null,
     ) {
     }
 
@@ -32,6 +33,7 @@
                 'constraint' => type_optional(type_string()),
                 'conflict_columns' => type_list(type_string()),
                 'update_columns' => type_list(type_string()),
+                'preserve_existing_values' => type_optional(type_boolean()),
             ]
         )->assert($options);
 
@@ -40,6 +42,7 @@
             $options['constraint'] ?? null,
             $options['conflict_columns'] ?? [],
             $options['update_columns'] ?? [],
+            $options['preserve_existing_values'] ?? null,
         );
     }
 
Index: src/lib/doctrine-dbal-bulk/src/Flow/Doctrine/Bulk/Dialect/PostgreSQLDialect.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/lib/doctrine-dbal-bulk/src/Flow/Doctrine/Bulk/Dialect/PostgreSQLDialect.php b/src/lib/doctrine-dbal-bulk/src/Flow/Doctrine/Bulk/Dialect/PostgreSQLDialect.php
--- a/src/lib/doctrine-dbal-bulk/src/Flow/Doctrine/Bulk/Dialect/PostgreSQLDialect.php	(revision 12eda07dc13619a72f7e75c0a4206eb93696c7ed)
+++ b/src/lib/doctrine-dbal-bulk/src/Flow/Doctrine/Bulk/Dialect/PostgreSQLDialect.php	(date 1767695103692)
@@ -67,7 +67,7 @@
                 $bulkData->toSqlPlaceholders(),
                 \implode(',', $options->conflictColumns),
                 \count($options->updateColumns)
-                    ? $this->updatedSelectedColumns($options->updateColumns, $bulkData->columns())
+                    ? $this->updatedSelectedColumns($options->updateColumns, $bulkData->columns(), $table->name(), $options->preserveExistingValues)
                     : $this->updateAllColumns($bulkData->columns())
             );
         }
@@ -80,7 +80,7 @@
                 $bulkData->toSqlPlaceholders(),
                 $options->constraint,
                 \count($options->updateColumns)
-                    ? $this->updatedSelectedColumns($options->updateColumns, $bulkData->columns())
+                    ? $this->updatedSelectedColumns($options->updateColumns, $bulkData->columns(), $table->name())
                     : $this->updateAllColumns($bulkData->columns())
             );
         }
@@ -132,7 +132,7 @@
             'UPDATE %s as existing_table SET %s FROM (VALUES %s) as excluded (%s) WHERE %s',
             $table->name(),
             \count($options->updateColumns)
-                ? $this->updatedSelectedColumns($options->updateColumns, $bulkData->columns()->without(...$options->primaryKeyColumns))
+                ? $this->updatedSelectedColumns($options->updateColumns, $bulkData->columns()->without(...$options->primaryKeyColumns), $table->name(), $options->preserveExistingValues)
                 : $this->updateAllColumns($bulkData->columns()->without(...$options->primaryKeyColumns)),
             $bulkData->toSqlCastedPlaceholders($table),
             \implode(',', \array_map(fn (string $column) : string => $this->platform->quoteIdentifier($column), $bulkData->columns()->all())),
@@ -173,10 +173,8 @@
     /**
      * @param array<string> $updateColumns
      * @param Columns $columns
-     *
-     * @return string
      */
-    private function updatedSelectedColumns(array $updateColumns, Columns $columns) : string
+    private function updatedSelectedColumns(array $updateColumns, Columns $columns, string $tableName, ?bool $preserveExistingValues = null) : string
     {
         /**
          * https://www.postgresql.org/docs/9.5/sql-insert.html#SQL-ON-CONFLICT
@@ -184,7 +182,14 @@
          * table's name (or an alias), and to rows proposed for insertion using the special EXCLUDED table.
          */
         return \count($updateColumns)
-            ? \implode(',', \array_map(fn (string $column) : string => "{$this->platform->quoteIdentifier($column)} = {$this->platform->quoteIdentifier('excluded.' . $column)}", $updateColumns))
+            ? \implode(',', \array_map(function (string $column) use ($tableName, $preserveExistingValues) : string {
+                $clause = "{$this->platform->quoteIdentifier($column)} = ";
+                if (true === $preserveExistingValues) {
+                    return $clause . "COALESCE({$this->platform->quoteIdentifier('excluded.' . $column)}, {$tableName}.{$this->platform->quoteIdentifier($column)})";
+                }
+
+                return $clause . "{$this->platform->quoteIdentifier('excluded.' . $column)}";
+            }, $updateColumns))
             : $this->updateAllColumns($columns);
     }
 }