squirrel icon indicating copy to clipboard operation
squirrel copied to clipboard

feat: implement 'MERGE' statement builder (PostgreSQL 15+)

Open sund3RRR opened this issue 5 months ago • 0 comments

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)
}

sund3RRR avatar Jul 22 '25 17:07 sund3RRR