Query Building: INSERT

Contents

• Basic example
• Getting the last inserted AUTO_INCREMENT/SERIAL ID
• INSERT and ignore duplicates
• Upsert
• INSERT from SELECT
• Binding values to columns
• Clause manipulation

INSERT INTO public.users (name, email)
VALUES ('bob', 'bob@email.com'), ('alice', 'alice@email.com');

u := tables.USERS()

// ...if you use ToSQL
query, args := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, err := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

--------------
-- Postgres --
--------------

INSERT INTO public.users (name, email)
VALUES ('bob', 'bob@email.com'), ('alice', 'alice@email.com')
RETURNING u.user_id;

-----------
-- MySQL --
-----------

INSERT INTO public.users (name, email)
VALUES ('bob', 'bob@email.com'), ('alice', 'alice@email.com');
SELECT last_insert_id();

u := tables.USERS()
var lastInsertID int64
var lastInsertIDs []int64

//----------//
// Postgres //
//----------//
// Note: Postgres is able to return multiple INSERT-ed IDs, not just
// the last inserted one

// ...if you use ToSQL
query, args := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    Returning(u.USER_ID).
    ToSQL()
rows, err := DB.Query(query, args...)
if err != nil {
    /* handle error */
}
for rows.Next() {
    err = rows.Scan(&lastInsertID)
    if err != nil {
        /* handle error */
    }
    lastInsertIDs = append(lastInsertIDs, lastInsertID)
}

// ...if you use Returningx (see [Struct Mapping](/sq/basics/struct-mapping.html#select-one-vs-many))
err := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    Returningx(func(row *sq.Row) {
        lastInsertID = row.Int64(u.USER_ID)
    }, func() {
        lastInsertIDs = append(lastInsertIDs, lastInsertID)
    }).
    Fetch(DB)
if err != nil {
    /* handle error */
}

// ...if you use ReturningRowx (see [Struct Mapping](/sq/basics/struct-mapping.html#select-one-vs-many))
// ReturningRowx only returns one row, which means in this case it will only
// return the user_id of 'bob'.
err := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    ReturningRowx(func(row *sq.Row) {
        lastInsertID = row.Int64(u.USER_ID)
    }).
    Fetch(DB)
if err != nil {
    /* handle error */
}

//-------//
// MySQL //
//-------//

// ...if you use ToSQL
query, args := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    Returning(u.USER_ID).
    ToSQL()
res, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}
lastInsertID, err := res.LastInsertId() // user_id for 'bob'
if err != nil {
    /* handle error */
}

// ...if you use Exec
lastInsertID, _, err := sq.InsertInto(u) // user_id for 'bob'
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    Exec(DB, sq.ElastInsertID)
if err != nil {
    /* handle error */
}

--------------
-- Postgres --
--------------

INSERT INTO public.users (name, email)
VALUES ('bob', 'bob@email.com'), ('alice', 'alice@email.com')
ON CONFLICT DO NOTHING;

-----------
-- MySQL --
-----------

-- Ignoring all errors ([including wrong datatype and foreign key errors](https://stackoverflow.com/a/4596409))
-- Only use if you really don't care about any possible errors
INSERT IGNORE INTO public.users (name, email)
VALUES ('bob', 'bob@email.com'), ('alice', 'alice@email.com');

-- Ignoring duplicates by assigning a column to itself
-- (MySQL will not run UPDATE if the value is the same)
INSERT INTO public.users (name, email)
VALUES ('bob', 'bob@email.com'), ('alice', 'alice@email.com')
ON DUPLICATE KEY DO UPDATE email = email;

u := tables.USERS()

//----------//
// Postgres //
//----------//

// ...if you use ToSQL
query, args := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    OnConflict().DoNothing().
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, err := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    OnConflict().DoNothing().
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

//-------//
// MySQL //
//-------//

// Ignoring all errors

// ...if you use ToSQL
query, args := sq.InsertIgnoreInto(u)
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, _, err := sq.InsertIgnoreInto(u)
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

// Ignoring duplicates by assigning a column to itself

// ...if you use ToSQL
query, args := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    OnDuplicateKeyUpdate(u.EMAIL.Set(u.EMAIL)).
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, _, err := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    OnDuplicateKeyUpdate(u.EMAIL.Set(u.EMAIL)).
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

--------------
-- Postgres --
--------------
-- [postgrestutorial.com: upsert](https://www.postgresqltutorial.com/postgresql-upsert/)

-- Using conflict column(s)
INSERT INTO public.users (name, email)
VALUES ('bob', 'bob@email.com'), ('alice', 'alice@email.com')
ON CONFLICT (email) DO UPDATE SET
name = EXCLUDED.name;

-- Using conflict constraint
INSERT INTO public.users (name, email)
VALUES ('bob', 'bob@email.com'), ('alice', 'alice@email.com')
ON CONFLICT ON CONSTRAINT users_email_key DO UPDATE SET
name = EXCLUDED.name;

-----------
-- MySQL --
-----------
-- [mysqltutorial.org: upsert](https://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update/)

INSERT INTO public.users (name, email)
VALUES ('bob', 'bob@email.com'), ('alice', 'alice@email.com')
ON DUPLICATE KEY DO UPDATE
name = VALUES(name);

u := tables.USERS()

//----------//
// Postgres //
//----------//

// Using conflict columns(s)

// ...if you use ToSQL
query, args := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    OnConflict(u.EMAIL).
    DoUpdateSet(
        u.NAME.Set(sq.Excluded(u.NAME)),
    ).
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, err := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    OnConflict(u.EMAIL).
    DoUpdateSet(
        u.NAME.Set(sq.Excluded(u.NAME)),
    ).
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

// Using conflict constraint

// ...if you use ToSQL
query, args := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    OnConflictOnConstraint("users_email_key").
    DoUpdateSet(
        u.NAME.Set(sq.Excluded(u.NAME)),
    ).
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, err := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    OnConflictOnConstraint("users_email_key").
    DoUpdateSet(
        u.NAME.Set(sq.Excluded(u.NAME)),
    ).
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

//-------//
// MySQL //
//-------//

// ...if you use ToSQL
query, args := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    OnDuplicateKeyUpdate(
        u.NAME.Set(sq.Values(u.NAME)),
    ).
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, _, err := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Values("bob", "bob@email.com").
    Values("alice", "alice@email.com").
    OnDuplicateKeyUpdate(
        u.NAME.Set(sq.Values(u.NAME)),
    ).
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

INSERT INTO public.users (name, email)
SELECT name, email
FROM public.users
WHERE users.name = 'bob';

u := tables.USERS()

// ...if you use ToSQL
query, args := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Select(sq.
        Select(u.NAME, u.EMAIL).
        From(u).
        Where(u.NAME.EqString("bob")),
    ).
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, err := sq.InsertInto(u).
    Columns(u.NAME, u.EMAIL).
    Select(sq.
        Select(u.NAME, u.EMAIL).
        From(u).
        Where(u.NAME.EqString("bob")),
    ).
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

Alternatively, you can bind values to columns in an insert statement. This helps if you are inserting a lot of columns and you don't want to accidentally mess up the order or miss a value.

Inserting one entry

-- SQL
INSERT INTO public.users (name, email)
VALUES ('bob', 'bob@email.com');

// Go
u := tables.USERS()
user := User{Name: "bob", Email: "bob@email.com"}

// ...if you use ToSQL
query, args := sq.InsertInto(u).
    Valuesx(func(col *sq.Column) {
        col.SetString(u.NAME, user.Name)
        col.SetString(u.EMAIL, user.Email)
    }).
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, err := sq.InsertInto(u).
    Valuesx(func(col *sq.Column) {
        col.SetString(u.NAME, user.Name)
        col.SetString(u.EMAIL, user.Email)
    }).
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

Inserting many entries

-- SQL
INSERT INTO public.users (name, email)
VALUES
    ('bob', 'bob@email.com'),
    ('alice', 'alice@email.com'),
    ('eve', 'eve@email.com');

// Go
u := tables.USERS()
users := []User{
    {Name: "bob",   Email: "bob@email.com"},
    {Name: "alice", Email: "alice@email.com"},
    {Name: "eve",   Email: "eve@email.com"},
}

// ...if you use ToSQL
query, args := sq.InsertInto(u).
    Valuesx(func(col *sq.Column) {
        for _, user := range users {
            col.SetString(u.NAME, user.Name)
            col.SetString(u.EMAIL, user.Email)
        }
    }).
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, err := sq.InsertInto(u).
    Valuesx(func(col *sq.Column) {
        for _, user := range users {
            col.SetString(u.NAME, user.Name)
            col.SetString(u.EMAIL, user.Email)
        }
    }).
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

• WITH (postgres only)   clauses
  For more information see Common Table Expressions.

  var q sq.InsertQuery
  
  // Adding
  
  q = q.With(cte1, cte2, cte3)
  // is equivalent to //
  q = q.With(cte1).With(cte2).With(cte3)
  // is equivalent to //
  q.CTEs = []sq.CTE{cte1, cte2, cte3}
  
  // Removing
  
  q.CTEs = nil
  

• INSERT INTO   clauses

  var q sq.InsertQuery
  
  // Adding
  
  q = q.InsertInto(table)
  // is equivalent to //
  q.IntoTable = table
  
  // Removing
  
  q.IntoTable = nil
  

• INSERT IGNORE INTO (mysql only)   clauses

  var q sq.InsertQuery
  
  // Adding
  
  q = q.InsertIgnoreInto(table)
  // is equivalent to //
  q.Ignore = true
  q.IntoTable = table
  
  // Removing
  
  q.Ignore = false
  q.IntoTable = nil
  

• VALUES   clauses

  var q sq.InsertQuery
  
  // Adding
  
  q = q.Columns(field1, field2, field3).
      Values(value1, value2, value3).
      Values(value4, value5, value6)
  // is equivalent to //
  q = q.InsertRow(field1.Set(value1), field2.Set(value2), field3.Set(value3)).
    InsertRow(field1.Set(value4), field2.Set(value5), field3.Set(value6))
  // is equivalent to //
  q.InsertColumns = []sq.Field{field1, field2, field3}
  q.RowValues = []sq.RowValue{
      {value1, value2, value3},
      {value4, value5, value6},
  }
  
  // Removing
  
  q.InsertColumns = nil
  q.RowValues = nil
  

• SELECT   clauses

  var q sq.InsertQuery
  
  // Adding
  
  q = q.Select(sq.Select(field1, field2).From(table).Where(predicate)).
  // is equivalent to //
  subquery := sq.Select(field1, field2).From(table).Where(predicate)
  q.SelectQuery = &subquery
  
  // Removing
  
  q.SelectQuery = nil
  

• ON CONFLICT (postgres only)   clauses

  var q sq.InsertQuery
  
  // Adding
  
  /* ON CONFLICT DO NOTHING */
  q = q.OnConflict().DoNothing()
  // is equivalent to //
  q.HandleConflict = true
  
  /* ON CONFLICT DO UPDATE */
  q = q.OnConflict(field1, field2).DoUpdateSet(assignment1, assignment2)
  // is equivalent to //
  q.HandleConflict = true
  q.ConflictFields = []sq.Field{field1, field2}
  q.Resolution = []sq.Assignment{assignment1, assignment2}
  
  /* ON CONFLICT DO UPDATE with predicates */
  q = q.OnConflict(field1, field2).
      Where(predicate1).
      DoUpdateSet(assignment1, assignment2).
      Where(predicate2)
  // is equivalent to //
  q.HandleConflict = true
  q.ConflictFields = []sq.Field{field1, field2}
  q.ConflictPredicate.Predicates = []sq.Predicate{predicate1}
  q.Resolution = []sq.Assignment{assignment1, assignment2}
  q.ResolutionPredicate.Predicates = []sq.Predicate{predicate2}
  
  // Removing
  
  q.HandleConflict = false
  /* optional */
  q.ConflictFields = nil
  q.ConflictPredicate.Predicates = nil
  q.Resolution = nil
  q.ResolutionPredicate.Predicates = nil
  

• ON DUPLICATE KEY (mysql only)   clauses

  var q sq.InsertQuery
  
  // Adding
  
  q = q.OnDuplicateKeyUpdate(assignment1, assignment2)
  // is equivalent to //
  q.Resolution = []sq.Assignment{assignment1, assignment2}
  
  // Removing
  
  q.Resolution = nil
  

• RETURNING (postgres only)   clauses

  var q sq.InsertQuery
  
  // Adding
  
  q = q.Returning(field1, field2, field3)
  // is equivalent to //
  q = q.Returning(field1).Returning(field2).Returning(field3)
  // is equivalent to //
  q.ReturningFields = []sq.Field{field1, field2, field3}
  
  // Removing
  
  q.ReturningFields = nil