1# Inserting
2
3* [Creating An InsertDataset](#create)
4* Examples
5 * [Insert Cols and Vals](#insert-cols-vals)
6 * [Insert `goqu.Record`](#insert-record)
7 * [Insert Structs](#insert-structs)
8 * [Insert Map](#insert-map)
9 * [Insert From Query](#insert-from-query)
10 * [Returning](#returning)
11 * [SetError](#seterror)
12 * [Executing](#executing)
13
14<a name="create"></a>
15To create a [`InsertDataset`](https://godoc.org/github.com/doug-martin/goqu/#InsertDataset) you can use
16
17**[`goqu.Insert`](https://godoc.org/github.com/doug-martin/goqu/#Insert)**
18
19When you just want to create some quick SQL, this mostly follows the `Postgres` with the exception of placeholders for prepared statements.
20
21```go
22ds := goqu.Insert("user").Rows(
23 goqu.Record{"first_name": "Greg", "last_name": "Farley"},
24)
25insertSQL, _, _ := ds.ToSQL()
26fmt.Println(insertSQL, args)
27```
28Output:
29```
30INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley')
31```
32
33**[`SelectDataset.Insert`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.Insert)**
34
35If you already have a `SelectDataset` you can invoke `Insert()` to get a `InsertDataset`
36
37**NOTE** This method will also copy over the `WITH` clause as well as the `FROM`
38
39```go
40ds := goqu.From("user")
41
42ds := ds.Insert().Rows(
43 goqu.Record{"first_name": "Greg", "last_name": "Farley"},
44)
45insertSQL, _, _ := ds.ToSQL()
46fmt.Println(insertSQL, args)
47```
48Output:
49```
50INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley')
51```
52
53**[`DialectWrapper.Insert`](https://godoc.org/github.com/doug-martin/goqu/#DialectWrapper.Insert)**
54
55Use this when you want to create SQL for a specific `dialect`
56
57```go
58// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"
59
60dialect := goqu.Dialect("mysql")
61
62ds := dialect.Insert().Rows(
63 goqu.Record{"first_name": "Greg", "last_name": "Farley"},
64)
65insertSQL, _, _ := ds.ToSQL()
66fmt.Println(insertSQL, args)
67```
68Output:
69```
70INSERT INTO `user` (`first_name`, `last_name`) VALUES ('Greg', 'Farley')
71```
72
73**[`Database.Insert`](https://godoc.org/github.com/doug-martin/goqu/#DialectWrapper.Insert)**
74
75Use this when you want to execute the SQL or create SQL for the drivers dialect.
76
77```go
78// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"
79
80mysqlDB := //initialize your db
81db := goqu.New("mysql", mysqlDB)
82
83ds := db.Insert().Rows(
84 goqu.Record{"first_name": "Greg", "last_name": "Farley"},
85)
86insertSQL, _, _ := ds.ToSQL()
87fmt.Println(insertSQL, args)
88```
89Output:
90```
91INSERT INTO `user` (`first_name`, `last_name`) VALUES ('Greg', 'Farley')
92```
93
94### Examples
95
96For more examples visit the **[Docs](https://godoc.org/github.com/doug-martin/goqu/#InsertDataset)**
97
98<a name="insert-cols-vals"></a>
99**Insert with Cols and Vals**
100
101```go
102ds := goqu.Insert("user").
103 Cols("first_name", "last_name").
104 Vals(
105 goqu.Vals{"Greg", "Farley"},
106 goqu.Vals{"Jimmy", "Stewart"},
107 goqu.Vals{"Jeff", "Jeffers"},
108 )
109insertSQL, args, _ := ds.ToSQL()
110fmt.Println(insertSQL, args)
111```
112
113Output:
114```sql
115INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
116```
117
118<a name="insert-record"></a>
119**Insert `goqu.Record`**
120
121```go
122ds := goqu.Insert("user").Rows(
123 goqu.Record{"first_name": "Greg", "last_name": "Farley"},
124 goqu.Record{"first_name": "Jimmy", "last_name": "Stewart"},
125 goqu.Record{"first_name": "Jeff", "last_name": "Jeffers"},
126)
127insertSQL, args, _ := ds.ToSQL()
128fmt.Println(insertSQL, args)
129```
130
131Output:
132```
133INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
134```
135
136<a name="insert-structs"></a>
137**Insert Structs**
138
139```go
140type User struct {
141 FirstName string `db:"first_name"`
142 LastName string `db:"last_name"`
143}
144ds := goqu.Insert("user").Rows(
145 User{FirstName: "Greg", LastName: "Farley"},
146 User{FirstName: "Jimmy", LastName: "Stewart"},
147 User{FirstName: "Jeff", LastName: "Jeffers"},
148)
149insertSQL, args, _ := ds.ToSQL()
150fmt.Println(insertSQL, args)
151```
152
153Output:
154```
155INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
156```
157
158You can skip fields in a struct by using the `skipinsert` tag
159
160```go
161type User struct {
162 FirstName string `db:"first_name" goqu:"skipinsert"`
163 LastName string `db:"last_name"`
164}
165ds := goqu.Insert("user").Rows(
166 User{FirstName: "Greg", LastName: "Farley"},
167 User{FirstName: "Jimmy", LastName: "Stewart"},
168 User{FirstName: "Jeff", LastName: "Jeffers"},
169)
170insertSQL, args, _ := ds.ToSQL()
171fmt.Println(insertSQL, args)
172```
173
174Output:
175```
176INSERT INTO "user" ("last_name") VALUES ('Farley'), ('Stewart'), ('Jeffers') []
177```
178
179If you want to use the database `DEFAULT` when the struct field is a zero value you can use the `defaultifempty` tag.
180
181```go
182type User struct {
183 FirstName string `db:"first_name" goqu:"defaultifempty"`
184 LastName string `db:"last_name"`
185}
186ds := goqu.Insert("user").Rows(
187 User{LastName: "Farley"},
188 User{FirstName: "Jimmy", LastName: "Stewart"},
189 User{LastName: "Jeffers"},
190)
191insertSQL, args, _ := ds.ToSQL()
192fmt.Println(insertSQL, args)
193```
194
195Output:
196```
197INSERT INTO "user" ("first_name", "last_name") VALUES (DEFAULT, 'Farley'), ('Jimmy', 'Stewart'), (DEFAULT, 'Jeffers') []
198```
199
200`goqu` will also use fields in embedded structs when creating an insert.
201
202**NOTE** unexported fields will be ignored!
203
204```go
205type Address struct {
206 Street string `db:"address_street"`
207 State string `db:"address_state"`
208}
209type User struct {
210 Address
211 FirstName string
212 LastName string
213}
214ds := goqu.Insert("user").Rows(
215 User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
216 User{Address: Address{Street: "211 Street", State: "NY"}, FirstName: "Jimmy", LastName: "Stewart"},
217 User{Address: Address{Street: "311 Street", State: "NY"}, FirstName: "Jeff", LastName: "Jeffers"},
218)
219insertSQL, args, _ := ds.ToSQL()
220fmt.Println(insertSQL, args)
221```
222
223Output:
224```
225INSERT INTO "user" ("address_state", "address_street", "firstname", "lastname") VALUES ('NY', '111 Street', 'Greg', 'Farley'), ('NY', '211 Street', 'Jimmy', 'Stewart'), ('NY', '311 Street', 'Jeff', 'Jeffers') []
226```
227
228**NOTE** When working with embedded pointers if the embedded struct is nil then the fields will be ignored.
229
230```go
231type Address struct {
232 Street string
233 State string
234}
235type User struct {
236 *Address
237 FirstName string
238 LastName string
239}
240ds := goqu.Insert("user").Rows(
241 User{FirstName: "Greg", LastName: "Farley"},
242 User{FirstName: "Jimmy", LastName: "Stewart"},
243 User{FirstName: "Jeff", LastName: "Jeffers"},
244)
245insertSQL, args, _ := ds.ToSQL()
246fmt.Println(insertSQL, args)
247```
248
249Output:
250```
251INSERT INTO "user" ("firstname", "lastname") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
252```
253
254You can ignore an embedded struct or struct pointer by using `db:"-"`
255
256```go
257type Address struct {
258 Street string
259 State string
260}
261type User struct {
262 Address `db:"-"`
263 FirstName string
264 LastName string
265}
266
267ds := goqu.Insert("user").Rows(
268 User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
269 User{Address: Address{Street: "211 Street", State: "NY"}, FirstName: "Jimmy", LastName: "Stewart"},
270 User{Address: Address{Street: "311 Street", State: "NY"}, FirstName: "Jeff", LastName: "Jeffers"},
271)
272insertSQL, args, _ := ds.ToSQL()
273fmt.Println(insertSQL, args)
274```
275
276Output:
277```
278INSERT INTO "user" ("firstname", "lastname") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
279```
280
281<a name="insert-map"></a>
282**Insert `map[string]interface{}`**
283
284```go
285ds := goqu.Insert("user").Rows(
286 map[string]interface{}{"first_name": "Greg", "last_name": "Farley"},
287 map[string]interface{}{"first_name": "Jimmy", "last_name": "Stewart"},
288 map[string]interface{}{"first_name": "Jeff", "last_name": "Jeffers"},
289)
290insertSQL, args, _ := ds.ToSQL()
291fmt.Println(insertSQL, args)
292```
293
294Output:
295```
296INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
297```
298
299<a name="insert-from-query"></a>
300**Insert from query**
301
302```go
303ds := goqu.Insert("user").Prepared(true).
304 FromQuery(goqu.From("other_table"))
305insertSQL, args, _ := ds.ToSQL()
306fmt.Println(insertSQL, args)
307```
308
309Output:
310```
311INSERT INTO "user" SELECT * FROM "other_table" []
312```
313
314You can also specify the columns
315
316```go
317ds := goqu.Insert("user").Prepared(true).
318 Cols("first_name", "last_name").
319 FromQuery(goqu.From("other_table").Select("fn", "ln"))
320insertSQL, args, _ := ds.ToSQL()
321fmt.Println(insertSQL, args)
322```
323
324Output:
325```
326INSERT INTO "user" ("first_name", "last_name") SELECT "fn", "ln" FROM "other_table" []
327```
328
329<a name="returning"></a>
330**Returning Clause**
331
332Returning a single column example.
333
334```go
335sql, _, _ := goqu.Insert("test").
336 Rows(goqu.Record{"a": "a", "b": "b"}).
337 Returning("id").
338 ToSQL()
339fmt.Println(sql)
340```
341
342Output:
343```
344INSERT INTO "test" ("a", "b") VALUES ('a', 'b') RETURNING "id"
345```
346
347Returning multiple columns
348
349```go
350sql, _, _ = goqu.Insert("test").
351 Rows(goqu.Record{"a": "a", "b": "b"}).
352 Returning("a", "b").
353 ToSQL()
354fmt.Println(sql)
355```
356
357Output:
358```
359INSERT INTO "test" ("a", "b") VALUES ('a', 'b') RETURNING "a", "b"
360```
361
362Returning all columns
363
364```go
365sql, _, _ = goqu.Insert("test").
366 Rows(goqu.Record{"a": "a", "b": "b"}).
367 Returning(goqu.T("test").All()).
368 ToSQL()
369fmt.Println(sql)
370```
371
372Output:
373```
374INSERT INTO "test" ("a", "b") VALUES ('a', 'b') RETURNING "test".*
375```
376
377<a name="seterror"></a>
378**[`SetError`](https://godoc.org/github.com/doug-martin/goqu/#InsertDataset.SetError)**
379
380Sometimes while building up a query with goqu you will encounter situations where certain
381preconditions are not met or some end-user contraint has been violated. While you could
382track this error case separately, goqu provides a convenient built-in mechanism to set an
383error on a dataset if one has not already been set to simplify query building.
384
385Set an Error on a dataset:
386
387```go
388func GetInsert(name string, value string) *goqu.InsertDataset {
389
390 var ds = goqu.Insert("test")
391
392 if len(field) == 0 {
393 return ds.SetError(fmt.Errorf("name is empty"))
394 }
395
396 if len(value) == 0 {
397 return ds.SetError(fmt.Errorf("value is empty"))
398 }
399
400 return ds.Rows(goqu.Record{name: value})
401}
402
403```
404
405This error is returned on any subsequent call to `Error` or `ToSQL`:
406
407```go
408var field, value string
409ds = GetInsert(field, value)
410fmt.Println(ds.Error())
411
412sql, args, err = ds.ToSQL()
413fmt.Println(err)
414```
415
416Output:
417```
418name is empty
419name is empty
420```
421
422<a name="executing"></a>
423## Executing Inserts
424
425To execute INSERTS use [`Database.Insert`](https://godoc.org/github.com/doug-martin/goqu/#Database.Insert) to create your dataset
426
427### Examples
428
429**Executing an single Insert**
430```go
431db := getDb()
432
433insert := db.Insert("goqu_user").Rows(
434 goqu.Record{"first_name": "Jed", "last_name": "Riley", "created": time.Now()},
435).Executor()
436
437if _, err := insert.Exec(); err != nil {
438 fmt.Println(err.Error())
439} else {
440 fmt.Println("Inserted 1 user")
441}
442```
443
444Output:
445
446```
447Inserted 1 user
448```
449
450**Executing multiple inserts**
451
452```go
453db := getDb()
454
455users := []goqu.Record{
456 {"first_name": "Greg", "last_name": "Farley", "created": time.Now()},
457 {"first_name": "Jimmy", "last_name": "Stewart", "created": time.Now()},
458 {"first_name": "Jeff", "last_name": "Jeffers", "created": time.Now()},
459}
460
461insert := db.Insert("goqu_user").Rows(users).Executor()
462if _, err := insert.Exec(); err != nil {
463 fmt.Println(err.Error())
464} else {
465 fmt.Printf("Inserted %d users", len(users))
466}
467
468```
469
470Output:
471```
472Inserted 3 users
473```
474
475If you use the RETURNING clause you can scan into structs or values.
476
477```go
478db := getDb()
479
480insert := db.Insert("goqu_user").Returning(goqu.C("id")).Rows(
481 goqu.Record{"first_name": "Jed", "last_name": "Riley", "created": time.Now()},
482).Executor()
483
484var id int64
485if _, err := insert.ScanVal(&id); err != nil {
486 fmt.Println(err.Error())
487} else {
488 fmt.Printf("Inserted 1 user id:=%d\n", id)
489}
490```
491
492Output:
493
494```
495Inserted 1 user id:=5
496```
View as plain text