jschaf
commented
2 years ago would you know if there's a difference on the DB side performance wise?
It's almost certainly faster to insert multiple rows in a single insert since you avoid all the transaction overhead but I have no idea how much faster.
Using a variable number of VALUES expressions requires dynamic SQL which something I want but haven't figured how to go about it. However, there few options that work as detailed below:
Schema
CREATE TABLE users (
username text,
password text,
created_at timestamptz
);Option 1: Insert many rows using unnest with arrays of columns
This is the approach I've been using. Seems like the most straight-forward way to me.
query.sql
-- name: InsertMany :many
WITH
input AS (
SELECT username, password, created_at
FROM unnest(
pggen.arg('usernames')::bigint[],
pggen.arg('passwords')::text[],
pggen.arg('created_ats')::timestamptz[]
) t(username, password, created_at)
)
INSERT INTO users (username, password, created_at)
SELECT username, password, created_at
FROM input
RETURNING *;This generates:
type InsertManyParams struct {
Usernames []int
Passwords []string
CreatedAts pgtype.TimestamptzArray
}
type InsertManyRow struct {
Username string `json:"username"`
Password string `json:"password"`
CreatedAt time.Time `json:"created_at"`
}
// InsertMany implements Querier.InsertMany.
func (q *DBQuerier) InsertMany(ctx context.Context, params InsertManyParams) ([]InsertManyRow, error) {}Option 2: Insert many rows using the table composite type
If you're inserting all the columns of a table you can take advantage of the fact that each table is also a composite type. So for your user table, there's also a user composite type. I've renamed to users since user is a reserved keyword and IntelliJ doesn't like it.
NOTE: this approach fail if you have any generated columns.
query.sql
-- name: InsertManyValues :many
INSERT INTO users
SELECT *
FROM unnest(pggen.arg('all_users')::users[])
RETURNING *;Which generates:
// Users represents the Postgres composite type "users".
type Users struct {
Username string `json:"username"`
Password string `json:"password"`
CreatedAt time.Time `json:"created_at"`
}
type InsertManyValuesRow struct {
Username string `json:"username"`
Password string `json:"password"`
CreatedAt time.Time `json:"created_at"`
}
// InsertManyValues implements Querier.InsertManyValues.
func (q *DBQuerier) InsertManyValues(ctx context.Context, allUsers []Users) ([]InsertManyValuesRow, error) {}Option 3: Insert many rows but only a subset of columns
Let's say created_at has a default value that we're happy with but we want to use a composite type instead of an array for each column. Our schema changes to:
CREATE TABLE users (
username text,
password text,
created_at timestamptz DEFAULT now()
);First, we need a composite type of the row we want to insert:
CREATE TYPE partial_user AS (
username text,
password text
);query.sql
-- name: InsertPartial :many
INSERT INTO users (username, password)
SELECT *
FROM unnest(pggen.arg('partial_users')::partial_user[])
RETURNING *;Which generates:
// PartialUser represents the Postgres composite type "partial_user".
type PartialUser struct {
Username string `json:"username"`
Password string `json:"password"`
}
type InsertPartialRow struct {
Username string `json:"username"`
Password string `json:"password"`
CreatedAt time.Time `json:"created_at"`
}
// InsertPartial implements Querier.InsertPartial.
func (q *DBQuerier) InsertPartial(ctx context.Context, partialUsers []PartialUser) ([]InsertPartialRow, error) {}
hebime
Hello,
Is it possible to generate an insert func that can insert multiple rows at once? Ex:
I understand that there's a batch API available but that still executes N queries on the DB vs just one with multiple value rows. At that point, would you know if there's a difference on the DB side performance wise?
Thanks!