Skip to content
Search

Merge Query Builder

Back
Table of Contents

The Merge Query Builder has a fluent, type-safe interface that builds PostgreSQL MERGE queries. MERGE (also known as "upsert" in some contexts) does INSERT, UPDATE, or DELETE operations in one statement. The operation depends on whether rows of a source table and a target table match.

Simple Merge with Update

<?php

use function Flow\PostgreSql\DSL\{merge, col, eq};

$query = merge('target_table', 't')
    ->using('source_table', 's')
    ->on(eq(col('t.id'), col('s.id')))
    ->whenMatched()
    ->thenUpdate([
        'name' => col('s.name'),
        'value' => col('s.value'),
    ]);

echo $query->toSql();
// MERGE INTO target_table t USING source_table s ON t.id = s.id WHEN MATCHED THEN UPDATE SET name = s.name, value = s.value

Merge with Delete

<?php

use function Flow\PostgreSql\DSL\{merge, col, eq};

$query = merge('target_table', 't')
    ->using('source_table', 's')
    ->on(eq(col('t.id'), col('s.id')))
    ->whenMatched()
    ->thenDelete();

echo $query->toSql();
// MERGE INTO target_table t USING source_table s ON t.id = s.id WHEN MATCHED THEN DELETE

Merge with Insert

<?php

use function Flow\PostgreSql\DSL\{merge, col, eq};

$query = merge('customers')
    ->using('new_customers', 'nc')
    ->on(eq(col('customers.id'), col('nc.id')))
    ->whenNotMatched()
    ->thenInsert(
        ['id', 'name', 'email'],
        [col('nc.id'), col('nc.name'), col('nc.email')]
    );

echo $query->toSql();
// MERGE INTO customers USING new_customers nc ON customers.id = nc.id WHEN NOT MATCHED THEN INSERT (id, name, email) VALUES (nc.id, nc.name, nc.email)

Merge with Insert Values

Alternative syntax using column => value pairs:

<?php

use function Flow\PostgreSql\DSL\{merge, col, eq, literal};

$query = merge('users')
    ->using('new_users', 'n')
    ->on(eq(col('users.id'), col('n.id')))
    ->whenNotMatched()
    ->thenInsertValues([
        'id' => col('n.id'),
        'name' => col('n.name'),
        'status' => literal('active'),
    ]);

echo $query->toSql();
// MERGE INTO users USING new_users n ON users.id = n.id WHEN NOT MATCHED THEN INSERT (id, name, status) VALUES (n.id, n.name, 'active')

Multiple WHEN Clauses

Combine different actions based on matching conditions:

<?php

use function Flow\PostgreSql\DSL\{merge, col, eq};

$query = merge('inventory', 'i')
    ->using('updates', 'u')
    ->on(eq(col('i.product_id'), col('u.product_id')))
    ->whenMatched()
    ->thenUpdate([
        'quantity' => col('u.quantity'),
        'updated_at' => col('u.updated_at'),
    ])
    ->whenNotMatched()
    ->thenInsert(
        ['product_id', 'quantity'],
        [col('u.product_id'), col('u.quantity')]
    );

echo $query->toSql();
// MERGE INTO inventory i USING updates u ON i.product_id = u.product_id WHEN MATCHED THEN UPDATE SET quantity = u.quantity, updated_at = u.updated_at WHEN NOT MATCHED THEN INSERT (product_id, quantity) VALUES (u.product_id, u.quantity)

Conditional WHEN Clauses

Add conditions to the WHEN MATCHED and WHEN NOT MATCHED clauses:

<?php

use function Flow\PostgreSql\DSL\{merge, col, eq, gt, literal};

$query = merge('products', 'p')
    ->using('price_updates', 'pu')
    ->on(eq(col('p.id'), col('pu.product_id')))
    ->whenMatchedAnd(gt(col('pu.price'), literal(0)))
    ->thenUpdate([
        'price' => col('pu.price'),
    ]);

echo $query->toSql();
// MERGE INTO products p USING price_updates pu ON p.id = pu.product_id WHEN MATCHED AND pu.price > 0 THEN UPDATE SET price = pu.price

DO NOTHING Action

<?php

use function Flow\PostgreSql\DSL\{merge, col, eq};

$query = merge('products')
    ->using('updates', 'u')
    ->on(eq(col('products.id'), col('u.id')))
    ->whenMatched()
    ->thenDoNothing();

echo $query->toSql();
// MERGE INTO products USING updates u ON products.id = u.id WHEN MATCHED THEN DO NOTHING

Merge Using Subquery

Use a subquery as the source:

<?php

use function Flow\PostgreSql\DSL\{merge, col, eq, select, table};

$sourceQuery = select()
    ->select(col('id'), col('name'), col('email'))
    ->from(table('staged_data'));

$query = merge('users')
    ->using($sourceQuery, 'src')
    ->on(eq(col('users.id'), col('src.id')))
    ->whenMatched()
    ->thenUpdate([
        'name' => col('src.name'),
        'email' => col('src.email'),
    ]);

echo $query->toSql();
// MERGE INTO users USING (SELECT id, name, email FROM staged_data) src ON users.id = src.id WHEN MATCHED THEN UPDATE SET name = src.name, email = src.email

Merge with CTE (WITH Clause)

<?php

use function Flow\PostgreSql\DSL\{with, col, eq, select, cte, table, merge};

$stagedData = select()
    ->select(col('id'), col('name'))
    ->from(table('raw_input'));

$query = with(
    cte('staged_data', $stagedData)
)->merge('users')
    ->using('staged_data', 's')
    ->on(eq(col('users.id'), col('s.id')))
    ->whenMatched()
    ->thenUpdate([
        'name' => col('s.name'),
    ]);

echo $query->toSql();
// WITH staged_data AS (SELECT id, name FROM raw_input) MERGE INTO users USING staged_data s ON users.id = s.id WHEN MATCHED THEN UPDATE SET name = s.name

Merge with Parameters

Use positional parameters for prepared statements:

<?php

use function Flow\PostgreSql\DSL\{merge, col, eq, param};

$query = merge('accounts')
    ->using('transactions', 't')
    ->on(eq(col('accounts.id'), col('t.account_id')))
    ->whenMatched()
    ->thenUpdate([
        'balance' => param(1),
    ]);

echo $query->toSql();
// MERGE INTO accounts USING transactions t ON accounts.id = t.account_id WHEN MATCHED THEN UPDATE SET balance = $1

WHEN NOT MATCHED BY SOURCE

Handle the rows in the target that have no matching rows in the source:

<?php

use function Flow\PostgreSql\DSL\{merge, col, eq};

$query = merge('target', 't')
    ->using('source', 's')
    ->on(eq(col('t.id'), col('s.id')))
    ->whenNotMatchedBySource()
    ->thenDelete();

echo $query->toSql();
// MERGE INTO target t USING source s ON t.id = s.id WHEN NOT MATCHED BY SOURCE THEN DELETE

Schema-Qualified Tables

<?php

use function Flow\PostgreSql\DSL\{merge, col, eq};

$query = merge('myschema.users', 'u')
    ->using('public.updates', 's')
    ->on(eq(col('u.id'), col('s.id')))
    ->whenMatched()
    ->thenDoNothing();

echo $query->toSql();
// MERGE INTO myschema.users u USING public.updates s ON u.id = s.id WHEN MATCHED THEN DO NOTHING

DSL Functions

For a complete list of DSL functions, see the DSL reference.

Found a typo or an outdated section? Edit this page on GitHub

Contributors

Built in the open.

Join us on GitHub
scroll back to top