feat: implement 'MERGE' statement builder (PostgreSQL 15+)
First of all, thank you for maintaining this cool project!
This PR introduces support for building MERGE statements, which were added to PostgreSQL starting from version 15. The new builder enables developers to construct MERGE INTO ... USING ... ON ... WHEN ... queries in the same fluent, composable style as other Squirrel statements (SELECT, INSERT, UPDATE, etc.).
MERGE combines the capabilities of INSERT ... ON CONFLICT ... DO UPDATE and conditional UPDATE logic in a single statement.
It is especially useful for batch operations and upserts with complex matching conditions, reducing the need for multiple round trips to the database.
More about the MERGE statement: https://www.postgresql.org/docs/current/sql-merge.html
Example usage:
builder := squirrel.
Merge("orders AS o").
ValuesAlias("vals").
Columns("order_id", "currency", "seller", "update_ts", "create_ts").
On("o.order_id = vals.order_id").
When(`
MATCHED THEN
UPDATE SET
update_ts = vals.update_ts,
`).
When(`
NOT MATCHED THEN
INSERT (
order_id,
currency,
seller,
brand_name,
update_ts,
create_ts
)
VALUES (
vals.order_id,
vals.currency,
vals.seller,
vals.brand_name,
vals.update_ts,
vals.create_ts
)
`).
Suffix("RETURNING merge_action(), o.*").
PlaceholderFormat(squirrel.Dollar)
values := [][]interface{}{
{1, "USD", "Amazon", "Adidas", "2020-01-01 00:00:00", "2020-01-01 00:00:00"},
{2, "USD", "Amazon", "Nike", "2020-01-01 00:00:00", "2020-01-01 00:00:00"},
}
for _, v := range values {
builder = builder.Values(v...)
}
sql, args, err := builder.ToSql()
if err != nil {
log.Fatal(err)
}