Documentation
¶
Overview ¶
Package upsert builds PostgreSQL INSERT ... ON CONFLICT statements.
Build assembles the statement from options and returns it with its argument list, ready for the Exec method of a sql.Connection:
query, args, err := upsert.Build(
upsert.WithTable("orders"),
upsert.WithConstraints("id"),
upsert.WithInsertValues(upsert.ColumnMap{"id": id, "total": total}),
upsert.WithOnConflictUpdate(upsert.ColumnMap{"total": total}),
)
if err != nil {
return err
}
_, err = conn.Exec(ctx, query, args...)
The conflict action is either an update or a no-op: WithOnConflictUpdate writes DO UPDATE SET, WithOnConflictDoNothing writes DO NOTHING, and asking for both returns ErrIncompatibleOptions. Either one needs at least one constraint column, since that is what names the conflict target.
Unlike the option sets elsewhere in this repository, the options here are validated - but at Build time, not as they are applied, so the error names the combination rather than the individual call.
Placeholders are PostgreSQL-style ($1, $2, ...). The builder is squirrel.
Index ¶
Examples ¶
Constants ¶
This section is empty.
Variables ¶
var ( ErrInvalidConstraint = errors.New("must set at least one constraint") ErrInsertValuesIsEmpty = errors.New("insert values must not be empty") ErrUpdateValuesIsEmpty = errors.New("update values must not be empty") ErrIncompatibleOptions = errors.New("incompatible options (WithOnConflictDoNothing, WithOnConflictUpdate)") )
Functions ¶
func Build ¶
Example ¶
package main
import (
"fmt"
"github.com/diegodesousas/go-devkit/pkg/database/sql/upsert"
)
func main() {
query, args, err := upsert.Build(
upsert.WithTable("orders"),
upsert.WithConstraints("id"),
upsert.WithInsertValues(upsert.ColumnMap{"id": 1, "total": 250}),
upsert.WithOnConflictUpdate(upsert.ColumnMap{"total": 250}),
)
if err != nil {
panic(err)
}
fmt.Println(query)
fmt.Println(args...)
// The run of spaces before SET is padding left by the underlying builder.
// It is redundant whitespace, not a syntax error, and PostgreSQL ignores it.
}
Output: INSERT INTO orders (id,total) VALUES ($1,$2) ON CONFLICT(id) DO UPDATE SET total = $3 1 250 250
Example (DoNothing) ¶
WithOnConflictDoNothing turns the statement into an insert-if-absent.
package main
import (
"fmt"
"github.com/diegodesousas/go-devkit/pkg/database/sql/upsert"
)
func main() {
query, args, err := upsert.Build(
upsert.WithTable("orders"),
upsert.WithConstraints("id"),
upsert.WithInsertValues(upsert.ColumnMap{"id": 1}),
upsert.WithOnConflictDoNothing(),
)
if err != nil {
panic(err)
}
fmt.Println(query)
fmt.Println(args...)
}
Output: INSERT INTO orders (id) VALUES ($1) ON CONFLICT(id) DO NOTHING 1
Example (IncompatibleOptions) ¶
Asking for both conflict actions is rejected at Build time.
package main
import (
"fmt"
"github.com/diegodesousas/go-devkit/pkg/database/sql/upsert"
)
func main() {
_, _, err := upsert.Build(
upsert.WithTable("orders"),
upsert.WithConstraints("id"),
upsert.WithInsertValues(upsert.ColumnMap{"id": 1}),
upsert.WithOnConflictDoNothing(),
upsert.WithOnConflictUpdate(upsert.ColumnMap{"total": 250}),
)
fmt.Println(err)
}
Output: incompatible options (WithOnConflictDoNothing, WithOnConflictUpdate)
Types ¶
type Option ¶
type Option func(settings settings) settings
func WithConstraints ¶
func WithInsertValues ¶
func WithOnConflictDoNothing ¶
func WithOnConflictDoNothing() Option
Source Files