Insert [PostgreSQL MySQL]
- API
- Example
- Bulk-insert
- Upsert
- Maps
- INSERT … SELECT

Insert [PostgreSQL MySQL]

API

For the full list of supported methods, see InsertQueryopen in new window.

1. db.NewInsert().
2. With("cte_name", subquery).

4. Model(&strct).
5. Model(&slice).
6. Model(&map). // only map[string]interface{}

8. Column("col1", "col2"). // list of columns to insert
9. ExcludeColumn("col1"). // all columns except col1
10. ExcludeColumn("*"). // exclude all columns

12. Table("table1", "table2"). // quotes table names
13. TableExpr("table1 AS t1"). // arbitrary unsafe expression
14. TableExpr("(?) AS subq", subquery).
15. ModelTableExpr("table1 AS t1"). // overrides model table name

17. Value("col1", "expr1", arg1, arg2). // overrides column value

19. On("CONFLICT (id) DO UPDATE").
20. Set("col1 = EXCLUDED.col1").

22. WherePK(). // where using primary keys
23. Where("id = ?", 123).
24. Where("name LIKE ?", "my%").
25. Where("? = 123", bun.Ident("id")).
26. Where("id IN (?)", bun.In([]int64{1, 2, 3})).
27. Where("id IN (?)", subquery).
28. Where("FALSE").WhereOr("TRUE").
29. WhereGroup(" AND ", func(q *bun.SelectQuery) *bun.SelectQuery {
30. return q.WhereOr("id = 1").
31. WhereOr("id = 2")
32. }).

34. Returning("*").
35. Returning("col1, col2").
36. Returning("NULL"). // don't return anything

38. Exec(ctx)

Example

To insert data, define a model and use InsertQueryopen in new window:

1. book := &Book{Title: "hello"}

3. res, err := db.NewInsert().Model(book).Exec(ctx)

Bulk-insert

To bulk-insert models, use a slice:

1. books := []Book{book1, book2}
2. res, err := db.NewInsert().Model(&books).Exec(ctx)
3. if err != nil {
4. panic(err)
5. }

7. for _, book := range books {
8. fmt.Println(book.ID) // book id is scanned automatically
9. }

Upsert

To insert a new book or update the existing one:

1. _, err := db.NewInsert().
2. Model(&book).
3. On("CONFLICT (id) DO UPDATE").
4. Set("title = EXCLUDED.title").
5. Exec(ctx)

1. INSERT INTO "books" ("id", "title") VALUES (100, 'my title')
2. ON CONFLICT (id) DO UPDATE SET title = EXCLUDED.title

For MySQL use:

1. _, err := db.NewInsert().
2. Model(&book).
3. On("DUPLICATE KEY UPDATE").
4. Exec(ctx)

1. INSERT INTO `books` (`id`, `title`) VALUES (100, 'my title')
2. ON DUPLICATE KEY UPDATE `title` = VALUES(`title`)

To ignore duplicates, use Ignore with all databases:

1. _, err := db.NewInsert().
2. Model(&book).
3. Ignore().
4. Exec(ctx)

1. -- MySQL
2. INSERT IGNORE INTO `books` (`id`, `title`) VALUES (100, 'my title');

4. -- PostgreSQL
5. INSERT INTO `books` (`id`, `title`) VALUES (100, 'my title')
6. ON CONFLICT DO NOTHING;

Maps

To insert a map[string]interface{}:

1. values := map[string]interface{}{
2. "title": "title1",
3. "text":  "text1",
4. }
5. _, err := db.NewInsert().Model(&values).TableExpr("books").Exec()

1. INSERT INTO "books" ("title", "text") VALUES ('title1', 'text2')

INSERT … SELECT

To copy rows between tables:

1. _, err := db.NewInsert().
2. Table("books_backup").
3. Table("books").
4. Exec(ctx)

1. INSERT INTO "books_backup" SELECT * FROM "books"

You can also specify columns to copy:

1. _, err := db.NewInsert().
2. ColumnExpr("id, name").
3. Table("dest").
4. Table("src").
5. Exec(ctx)

1. INSERT INTO "dest" (id, name) SELECT id, name FROM "src"