insert_dataset_example_test.go

Documentation: github.com/doug-martin/goqu/v9

     1  // nolint:lll // SQL statements are long
     2  package goqu_test
     3  
     4  import (
     5  	"database/sql"
     6  	"fmt"
     7  	"time"
     8  
     9  	"github.com/doug-martin/goqu/v9"
    10  	_ "github.com/doug-martin/goqu/v9/dialect/postgres"
    11  )
    12  
    13  func ExampleInsert_goquRecord() {
    14  	ds := goqu.Insert("user").Rows(
    15  		goqu.Record{"first_name": "Greg", "last_name": "Farley"},
    16  		goqu.Record{"first_name": "Jimmy", "last_name": "Stewart"},
    17  		goqu.Record{"first_name": "Jeff", "last_name": "Jeffers"},
    18  	)
    19  	insertSQL, args, _ := ds.ToSQL()
    20  	fmt.Println(insertSQL, args)
    21  
    22  	// Output:
    23  	// INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
    24  }
    25  
    26  func ExampleInsert_map() {
    27  	ds := goqu.Insert("user").Rows(
    28  		map[string]interface{}{"first_name": "Greg", "last_name": "Farley"},
    29  		map[string]interface{}{"first_name": "Jimmy", "last_name": "Stewart"},
    30  		map[string]interface{}{"first_name": "Jeff", "last_name": "Jeffers"},
    31  	)
    32  	insertSQL, args, _ := ds.ToSQL()
    33  	fmt.Println(insertSQL, args)
    34  
    35  	// Output:
    36  	// INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
    37  }
    38  
    39  func ExampleInsert_struct() {
    40  	type User struct {
    41  		FirstName string `db:"first_name"`
    42  		LastName  string `db:"last_name"`
    43  	}
    44  	ds := goqu.Insert("user").Rows(
    45  		User{FirstName: "Greg", LastName: "Farley"},
    46  		User{FirstName: "Jimmy", LastName: "Stewart"},
    47  		User{FirstName: "Jeff", LastName: "Jeffers"},
    48  	)
    49  	insertSQL, args, _ := ds.ToSQL()
    50  	fmt.Println(insertSQL, args)
    51  
    52  	// Output:
    53  	// INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
    54  }
    55  
    56  func ExampleInsert_prepared() {
    57  	ds := goqu.Insert("user").Prepared(true).Rows(
    58  		goqu.Record{"first_name": "Greg", "last_name": "Farley"},
    59  		goqu.Record{"first_name": "Jimmy", "last_name": "Stewart"},
    60  		goqu.Record{"first_name": "Jeff", "last_name": "Jeffers"},
    61  	)
    62  	insertSQL, args, _ := ds.ToSQL()
    63  	fmt.Println(insertSQL, args)
    64  
    65  	// Output:
    66  	// INSERT INTO "user" ("first_name", "last_name") VALUES (?, ?), (?, ?), (?, ?) [Greg Farley Jimmy Stewart Jeff Jeffers]
    67  }
    68  
    69  func ExampleInsert_fromQuery() {
    70  	ds := goqu.Insert("user").Prepared(true).
    71  		FromQuery(goqu.From("other_table"))
    72  	insertSQL, args, _ := ds.ToSQL()
    73  	fmt.Println(insertSQL, args)
    74  
    75  	// Output:
    76  	// INSERT INTO "user" SELECT * FROM "other_table" []
    77  }
    78  
    79  func ExampleInsert_fromQueryWithCols() {
    80  	ds := goqu.Insert("user").Prepared(true).
    81  		Cols("first_name", "last_name").
    82  		FromQuery(goqu.From("other_table").Select("fn", "ln"))
    83  	insertSQL, args, _ := ds.ToSQL()
    84  	fmt.Println(insertSQL, args)
    85  
    86  	// Output:
    87  	// INSERT INTO "user" ("first_name", "last_name") SELECT "fn", "ln" FROM "other_table" []
    88  }
    89  
    90  func ExampleInsert_colsAndVals() {
    91  	ds := goqu.Insert("user").
    92  		Cols("first_name", "last_name").
    93  		Vals(
    94  			goqu.Vals{"Greg", "Farley"},
    95  			goqu.Vals{"Jimmy", "Stewart"},
    96  			goqu.Vals{"Jeff", "Jeffers"},
    97  		)
    98  	insertSQL, args, _ := ds.ToSQL()
    99  	fmt.Println(insertSQL, args)
   100  
   101  	// Output:
   102  	// INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
   103  }
   104  
   105  func ExampleInsertDataset_Executor_withRecord() {
   106  	db := getDB()
   107  	insert := db.Insert("goqu_user").Rows(
   108  		goqu.Record{"first_name": "Jed", "last_name": "Riley", "created": time.Now()},
   109  	).Executor()
   110  	if _, err := insert.Exec(); err != nil {
   111  		fmt.Println(err.Error())
   112  	} else {
   113  		fmt.Println("Inserted 1 user")
   114  	}
   115  
   116  	users := []goqu.Record{
   117  		{"first_name": "Greg", "last_name": "Farley", "created": time.Now()},
   118  		{"first_name": "Jimmy", "last_name": "Stewart", "created": time.Now()},
   119  		{"first_name": "Jeff", "last_name": "Jeffers", "created": time.Now()},
   120  	}
   121  	if _, err := db.Insert("goqu_user").Rows(users).Executor().Exec(); err != nil {
   122  		fmt.Println(err.Error())
   123  	} else {
   124  		fmt.Printf("Inserted %d users", len(users))
   125  	}
   126  
   127  	// Output:
   128  	// Inserted 1 user
   129  	// Inserted 3 users
   130  }
   131  
   132  func ExampleInsertDataset_Executor_recordReturning() {
   133  	db := getDB()
   134  
   135  	type User struct {
   136  		ID        sql.NullInt64 `db:"id"`
   137  		FirstName string        `db:"first_name"`
   138  		LastName  string        `db:"last_name"`
   139  		Created   time.Time     `db:"created"`
   140  	}
   141  
   142  	insert := db.Insert("goqu_user").Returning(goqu.C("id")).Rows(
   143  		goqu.Record{"first_name": "Jed", "last_name": "Riley", "created": time.Now()},
   144  	).Executor()
   145  	var id int64
   146  	if _, err := insert.ScanVal(&id); err != nil {
   147  		fmt.Println(err.Error())
   148  	} else {
   149  		fmt.Printf("Inserted 1 user id:=%d\n", id)
   150  	}
   151  
   152  	insert = db.Insert("goqu_user").Returning(goqu.Star()).Rows([]goqu.Record{
   153  		{"first_name": "Greg", "last_name": "Farley", "created": time.Now()},
   154  		{"first_name": "Jimmy", "last_name": "Stewart", "created": time.Now()},
   155  		{"first_name": "Jeff", "last_name": "Jeffers", "created": time.Now()},
   156  	}).Executor()
   157  	var insertedUsers []User
   158  	if err := insert.ScanStructs(&insertedUsers); err != nil {
   159  		fmt.Println(err.Error())
   160  	} else {
   161  		for _, u := range insertedUsers {
   162  			fmt.Printf("Inserted user: [ID=%d], [FirstName=%+s] [LastName=%s]\n", u.ID.Int64, u.FirstName, u.LastName)
   163  		}
   164  	}
   165  
   166  	// Output:
   167  	// Inserted 1 user id:=5
   168  	// Inserted user: [ID=6], [FirstName=Greg] [LastName=Farley]
   169  	// Inserted user: [ID=7], [FirstName=Jimmy] [LastName=Stewart]
   170  	// Inserted user: [ID=8], [FirstName=Jeff] [LastName=Jeffers]
   171  }
   172  
   173  func ExampleInsertDataset_Executor_scanStructs() {
   174  	db := getDB()
   175  
   176  	type User struct {
   177  		ID        sql.NullInt64 `db:"id" goqu:"skipinsert"`
   178  		FirstName string        `db:"first_name"`
   179  		LastName  string        `db:"last_name"`
   180  		Created   time.Time     `db:"created"`
   181  	}
   182  
   183  	insert := db.Insert("goqu_user").Returning("id").Rows(
   184  		User{FirstName: "Jed", LastName: "Riley"},
   185  	).Executor()
   186  	var id int64
   187  	if _, err := insert.ScanVal(&id); err != nil {
   188  		fmt.Println(err.Error())
   189  	} else {
   190  		fmt.Printf("Inserted 1 user id:=%d\n", id)
   191  	}
   192  
   193  	insert = db.Insert("goqu_user").Returning(goqu.Star()).Rows([]User{
   194  		{FirstName: "Greg", LastName: "Farley", Created: time.Now()},
   195  		{FirstName: "Jimmy", LastName: "Stewart", Created: time.Now()},
   196  		{FirstName: "Jeff", LastName: "Jeffers", Created: time.Now()},
   197  	}).Executor()
   198  	var insertedUsers []User
   199  	if err := insert.ScanStructs(&insertedUsers); err != nil {
   200  		fmt.Println(err.Error())
   201  	} else {
   202  		for _, u := range insertedUsers {
   203  			fmt.Printf("Inserted user: [ID=%d], [FirstName=%+s] [LastName=%s]\n", u.ID.Int64, u.FirstName, u.LastName)
   204  		}
   205  	}
   206  
   207  	// Output:
   208  	// Inserted 1 user id:=5
   209  	// Inserted user: [ID=6], [FirstName=Greg] [LastName=Farley]
   210  	// Inserted user: [ID=7], [FirstName=Jimmy] [LastName=Stewart]
   211  	// Inserted user: [ID=8], [FirstName=Jeff] [LastName=Jeffers]
   212  }
   213  
   214  func ExampleInsertDataset_FromQuery() {
   215  	insertSQL, _, _ := goqu.Insert("test").
   216  		FromQuery(goqu.From("test2").Where(goqu.C("age").Gt(10))).
   217  		ToSQL()
   218  	fmt.Println(insertSQL)
   219  	// Output:
   220  	// INSERT INTO "test" SELECT * FROM "test2" WHERE ("age" > 10)
   221  }
   222  
   223  func ExampleInsertDataset_ToSQL() {
   224  	type item struct {
   225  		ID      uint32 `db:"id" goqu:"skipinsert"`
   226  		Address string `db:"address"`
   227  		Name    string `db:"name"`
   228  	}
   229  	insertSQL, args, _ := goqu.Insert("items").Rows(
   230  		item{Name: "Test1", Address: "111 Test Addr"},
   231  		item{Name: "Test2", Address: "112 Test Addr"},
   232  	).ToSQL()
   233  	fmt.Println(insertSQL, args)
   234  
   235  	insertSQL, args, _ = goqu.Insert("items").Rows(
   236  		goqu.Record{"name": "Test1", "address": "111 Test Addr"},
   237  		goqu.Record{"name": "Test2", "address": "112 Test Addr"},
   238  	).ToSQL()
   239  	fmt.Println(insertSQL, args)
   240  
   241  	insertSQL, args, _ = goqu.Insert("items").Rows(
   242  		[]item{
   243  			{Name: "Test1", Address: "111 Test Addr"},
   244  			{Name: "Test2", Address: "112 Test Addr"},
   245  		}).ToSQL()
   246  	fmt.Println(insertSQL, args)
   247  
   248  	insertSQL, args, _ = goqu.From("items").Insert().Rows(
   249  		[]goqu.Record{
   250  			{"name": "Test1", "address": "111 Test Addr"},
   251  			{"name": "Test2", "address": "112 Test Addr"},
   252  		}).ToSQL()
   253  	fmt.Println(insertSQL, args)
   254  	// Output:
   255  	// INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
   256  	// INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
   257  	// INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
   258  	// INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
   259  }
   260  
   261  func ExampleInsertDataset_Prepared() {
   262  	type item struct {
   263  		ID      uint32 `db:"id" goqu:"skipinsert"`
   264  		Address string `db:"address"`
   265  		Name    string `db:"name"`
   266  	}
   267  
   268  	insertSQL, args, _ := goqu.Insert("items").Prepared(true).Rows(
   269  		item{Name: "Test1", Address: "111 Test Addr"},
   270  		item{Name: "Test2", Address: "112 Test Addr"},
   271  	).ToSQL()
   272  	fmt.Println(insertSQL, args)
   273  
   274  	insertSQL, args, _ = goqu.Insert("items").Prepared(true).Rows(
   275  		goqu.Record{"name": "Test1", "address": "111 Test Addr"},
   276  		goqu.Record{"name": "Test2", "address": "112 Test Addr"},
   277  	).ToSQL()
   278  	fmt.Println(insertSQL, args)
   279  
   280  	insertSQL, args, _ = goqu.Insert("items").Prepared(true).Rows(
   281  		[]item{
   282  			{Name: "Test1", Address: "111 Test Addr"},
   283  			{Name: "Test2", Address: "112 Test Addr"},
   284  		}).ToSQL()
   285  	fmt.Println(insertSQL, args)
   286  
   287  	insertSQL, args, _ = goqu.Insert("items").Prepared(true).Rows(
   288  		[]goqu.Record{
   289  			{"name": "Test1", "address": "111 Test Addr"},
   290  			{"name": "Test2", "address": "112 Test Addr"},
   291  		}).ToSQL()
   292  	fmt.Println(insertSQL, args)
   293  	// Output:
   294  	// INSERT INTO "items" ("address", "name") VALUES (?, ?), (?, ?) [111 Test Addr Test1 112 Test Addr Test2]
   295  	// INSERT INTO "items" ("address", "name") VALUES (?, ?), (?, ?) [111 Test Addr Test1 112 Test Addr Test2]
   296  	// INSERT INTO "items" ("address", "name") VALUES (?, ?), (?, ?) [111 Test Addr Test1 112 Test Addr Test2]
   297  	// INSERT INTO "items" ("address", "name") VALUES (?, ?), (?, ?) [111 Test Addr Test1 112 Test Addr Test2]
   298  }
   299  
   300  func ExampleInsertDataset_ClearRows() {
   301  	type item struct {
   302  		ID      uint32 `goqu:"skipinsert"`
   303  		Address string
   304  		Name    string
   305  	}
   306  	ds := goqu.Insert("items").Rows(
   307  		item{Name: "Test1", Address: "111 Test Addr"},
   308  		item{Name: "Test2", Address: "112 Test Addr"},
   309  	)
   310  	insertSQL, args, _ := ds.ClearRows().ToSQL()
   311  	fmt.Println(insertSQL, args)
   312  
   313  	// Output:
   314  	// INSERT INTO "items" DEFAULT VALUES []
   315  }
   316  
   317  func ExampleInsertDataset_Rows_withNoDbTag() {
   318  	type item struct {
   319  		ID      uint32 `goqu:"skipinsert"`
   320  		Address string
   321  		Name    string
   322  	}
   323  	insertSQL, args, _ := goqu.Insert("items").
   324  		Rows(
   325  			item{Name: "Test1", Address: "111 Test Addr"},
   326  			item{Name: "Test2", Address: "112 Test Addr"},
   327  		).
   328  		ToSQL()
   329  	fmt.Println(insertSQL, args)
   330  
   331  	insertSQL, args, _ = goqu.Insert("items").
   332  		Rows(
   333  			item{Name: "Test1", Address: "111 Test Addr"},
   334  			item{Name: "Test2", Address: "112 Test Addr"},
   335  		).
   336  		ToSQL()
   337  	fmt.Println(insertSQL, args)
   338  
   339  	insertSQL, args, _ = goqu.Insert("items").
   340  		Rows([]item{
   341  			{Name: "Test1", Address: "111 Test Addr"},
   342  			{Name: "Test2", Address: "112 Test Addr"},
   343  		}).
   344  		ToSQL()
   345  	fmt.Println(insertSQL, args)
   346  
   347  	// Output:
   348  	// INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
   349  	// INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
   350  	// INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
   351  }
   352  
   353  func ExampleInsertDataset_Rows_withGoquSkipInsertTag() {
   354  	type item struct {
   355  		ID      uint32 `goqu:"skipinsert"`
   356  		Address string
   357  		Name    string `goqu:"skipinsert"`
   358  	}
   359  	insertSQL, args, _ := goqu.Insert("items").
   360  		Rows(
   361  			item{Name: "Test1", Address: "111 Test Addr"},
   362  			item{Name: "Test2", Address: "112 Test Addr"},
   363  		).
   364  		ToSQL()
   365  	fmt.Println(insertSQL, args)
   366  
   367  	insertSQL, args, _ = goqu.Insert("items").
   368  		Rows([]item{
   369  			{Name: "Test1", Address: "111 Test Addr"},
   370  			{Name: "Test2", Address: "112 Test Addr"},
   371  		}).
   372  		ToSQL()
   373  	fmt.Println(insertSQL, args)
   374  
   375  	// Output:
   376  	// INSERT INTO "items" ("address") VALUES ('111 Test Addr'), ('112 Test Addr') []
   377  	// INSERT INTO "items" ("address") VALUES ('111 Test Addr'), ('112 Test Addr') []
   378  }
   379  
   380  func ExampleInsertDataset_Rows_withGoquDefaultIfEmptyTag() {
   381  	type item struct {
   382  		ID      uint32 `goqu:"skipinsert"`
   383  		Address string
   384  		Name    string `goqu:"defaultifempty"`
   385  	}
   386  	insertSQL, args, _ := goqu.Insert("items").
   387  		Rows(
   388  			item{Name: "Test1", Address: "111 Test Addr"},
   389  			item{Address: "112 Test Addr"},
   390  		).
   391  		ToSQL()
   392  	fmt.Println(insertSQL, args)
   393  
   394  	insertSQL, args, _ = goqu.Insert("items").
   395  		Rows([]item{
   396  			{Address: "111 Test Addr"},
   397  			{Name: "Test2", Address: "112 Test Addr"},
   398  		}).
   399  		ToSQL()
   400  	fmt.Println(insertSQL, args)
   401  
   402  	// Output:
   403  	// INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', DEFAULT) []
   404  	// INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', DEFAULT), ('112 Test Addr', 'Test2') []
   405  }
   406  
   407  func ExampleInsertDataset_Rows_withEmbeddedStruct() {
   408  	type Address struct {
   409  		Street string `db:"address_street"`
   410  		State  string `db:"address_state"`
   411  	}
   412  	type User struct {
   413  		Address
   414  		FirstName string
   415  		LastName  string
   416  	}
   417  	ds := goqu.Insert("user").Rows(
   418  		User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
   419  		User{Address: Address{Street: "211 Street", State: "NY"}, FirstName: "Jimmy", LastName: "Stewart"},
   420  		User{Address: Address{Street: "311 Street", State: "NY"}, FirstName: "Jeff", LastName: "Jeffers"},
   421  	)
   422  	insertSQL, args, _ := ds.ToSQL()
   423  	fmt.Println(insertSQL, args)
   424  
   425  	// Output:
   426  	// INSERT INTO "user" ("address_state", "address_street", "firstname", "lastname") VALUES ('NY', '111 Street', 'Greg', 'Farley'), ('NY', '211 Street', 'Jimmy', 'Stewart'), ('NY', '311 Street', 'Jeff', 'Jeffers') []
   427  }
   428  
   429  func ExampleInsertDataset_Rows_withIgnoredEmbedded() {
   430  	type Address struct {
   431  		Street string
   432  		State  string
   433  	}
   434  	type User struct {
   435  		Address   `db:"-"`
   436  		FirstName string
   437  		LastName  string
   438  	}
   439  	ds := goqu.Insert("user").Rows(
   440  		User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
   441  		User{Address: Address{Street: "211 Street", State: "NY"}, FirstName: "Jimmy", LastName: "Stewart"},
   442  		User{Address: Address{Street: "311 Street", State: "NY"}, FirstName: "Jeff", LastName: "Jeffers"},
   443  	)
   444  	insertSQL, args, _ := ds.ToSQL()
   445  	fmt.Println(insertSQL, args)
   446  
   447  	// Output:
   448  	// INSERT INTO "user" ("firstname", "lastname") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
   449  }
   450  
   451  func ExampleInsertDataset_Rows_withNilEmbeddedPointer() {
   452  	type Address struct {
   453  		Street string
   454  		State  string
   455  	}
   456  	type User struct {
   457  		*Address
   458  		FirstName string
   459  		LastName  string
   460  	}
   461  	ds := goqu.Insert("user").Rows(
   462  		User{FirstName: "Greg", LastName: "Farley"},
   463  		User{FirstName: "Jimmy", LastName: "Stewart"},
   464  		User{FirstName: "Jeff", LastName: "Jeffers"},
   465  	)
   466  	insertSQL, args, _ := ds.ToSQL()
   467  	fmt.Println(insertSQL, args)
   468  
   469  	// Output:
   470  	// INSERT INTO "user" ("firstname", "lastname") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
   471  }
   472  
   473  func ExampleInsertDataset_ClearOnConflict() {
   474  	type item struct {
   475  		ID      uint32 `db:"id" goqu:"skipinsert"`
   476  		Address string `db:"address"`
   477  		Name    string `db:"name"`
   478  	}
   479  	ds := goqu.Insert("items").OnConflict(goqu.DoNothing())
   480  	insertSQL, args, _ := ds.ClearOnConflict().Rows(
   481  		item{Name: "Test1", Address: "111 Test Addr"},
   482  		item{Name: "Test2", Address: "112 Test Addr"},
   483  	).ToSQL()
   484  	fmt.Println(insertSQL, args)
   485  
   486  	// Output:
   487  	// INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
   488  }
   489  
   490  func ExampleInsertDataset_OnConflict_doNothing() {
   491  	type item struct {
   492  		ID      uint32 `db:"id" goqu:"skipinsert"`
   493  		Address string `db:"address"`
   494  		Name    string `db:"name"`
   495  	}
   496  	insertSQL, args, _ := goqu.Insert("items").Rows(
   497  		item{Name: "Test1", Address: "111 Test Addr"},
   498  		item{Name: "Test2", Address: "112 Test Addr"},
   499  	).OnConflict(goqu.DoNothing()).ToSQL()
   500  	fmt.Println(insertSQL, args)
   501  
   502  	// Output:
   503  	// INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') ON CONFLICT DO NOTHING []
   504  }
   505  
   506  func ExampleInsertDataset_OnConflict_doUpdate() {
   507  	insertSQL, args, _ := goqu.Insert("items").
   508  		Rows(
   509  			goqu.Record{"name": "Test1", "address": "111 Test Addr"},
   510  			goqu.Record{"name": "Test2", "address": "112 Test Addr"},
   511  		).
   512  		OnConflict(goqu.DoUpdate("key", goqu.Record{"updated": goqu.L("NOW()")})).
   513  		ToSQL()
   514  	fmt.Println(insertSQL, args)
   515  
   516  	// Output:
   517  	// INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') ON CONFLICT (key) DO UPDATE SET "updated"=NOW() []
   518  }
   519  
   520  func ExampleInsertDataset_OnConflict_doUpdateWithWhere() {
   521  	type item struct {
   522  		ID      uint32 `db:"id" goqu:"skipinsert"`
   523  		Address string `db:"address"`
   524  		Name    string `db:"name"`
   525  	}
   526  	insertSQL, args, _ := goqu.Insert("items").
   527  		Rows([]item{
   528  			{Name: "Test1", Address: "111 Test Addr"},
   529  			{Name: "Test2", Address: "112 Test Addr"},
   530  		}).
   531  		OnConflict(goqu.DoUpdate(
   532  			"key",
   533  			goqu.Record{"updated": goqu.L("NOW()")}).Where(goqu.C("allow_update").IsTrue()),
   534  		).
   535  		ToSQL()
   536  	fmt.Println(insertSQL, args)
   537  
   538  	// Output:
   539  	// INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') ON CONFLICT (key) DO UPDATE SET "updated"=NOW() WHERE ("allow_update" IS TRUE) []
   540  }
   541  
   542  func ExampleInsertDataset_Returning() {
   543  	insertSQL, _, _ := goqu.Insert("test").
   544  		Returning("id").
   545  		Rows(goqu.Record{"a": "a", "b": "b"}).
   546  		ToSQL()
   547  	fmt.Println(insertSQL)
   548  	insertSQL, _, _ = goqu.Insert("test").
   549  		Returning(goqu.T("test").All()).
   550  		Rows(goqu.Record{"a": "a", "b": "b"}).
   551  		ToSQL()
   552  	fmt.Println(insertSQL)
   553  	insertSQL, _, _ = goqu.Insert("test").
   554  		Returning("a", "b").
   555  		Rows(goqu.Record{"a": "a", "b": "b"}).
   556  		ToSQL()
   557  	fmt.Println(insertSQL)
   558  	// Output:
   559  	// INSERT INTO "test" ("a", "b") VALUES ('a', 'b') RETURNING "id"
   560  	// INSERT INTO "test" ("a", "b") VALUES ('a', 'b') RETURNING "test".*
   561  	// INSERT INTO "test" ("a", "b") VALUES ('a', 'b') RETURNING "a", "b"
   562  }
   563  
   564  func ExampleInsertDataset_With() {
   565  	insertSQL, _, _ := goqu.Insert("foo").
   566  		With("other", goqu.From("bar").Where(goqu.C("id").Gt(10))).
   567  		FromQuery(goqu.From("other")).
   568  		ToSQL()
   569  	fmt.Println(insertSQL)
   570  
   571  	// Output:
   572  	// WITH other AS (SELECT * FROM "bar" WHERE ("id" > 10)) INSERT INTO "foo" SELECT * FROM "other"
   573  }
   574  
   575  func ExampleInsertDataset_WithRecursive() {
   576  	insertSQL, _, _ := goqu.Insert("num_count").
   577  		WithRecursive("nums(x)",
   578  			goqu.From().Select(goqu.L("1")).
   579  				UnionAll(goqu.From("nums").
   580  					Select(goqu.L("x+1")).Where(goqu.C("x").Lt(5))),
   581  		).
   582  		FromQuery(goqu.From("nums")).
   583  		ToSQL()
   584  	fmt.Println(insertSQL)
   585  	// Output:
   586  	// WITH RECURSIVE nums(x) AS (SELECT 1 UNION ALL (SELECT x+1 FROM "nums" WHERE ("x" < 5))) INSERT INTO "num_count" SELECT * FROM "nums"
   587  }
   588  
   589  func ExampleInsertDataset_Into() {
   590  	ds := goqu.Insert("test")
   591  	insertSQL, _, _ := ds.Into("test2").Rows(goqu.Record{"first_name": "bob", "last_name": "yukon"}).ToSQL()
   592  	fmt.Println(insertSQL)
   593  	// Output:
   594  	// INSERT INTO "test2" ("first_name", "last_name") VALUES ('bob', 'yukon')
   595  }
   596  
   597  func ExampleInsertDataset_Into_aliased() {
   598  	ds := goqu.Insert("test")
   599  	insertSQL, _, _ := ds.
   600  		Into(goqu.T("test").As("t")).
   601  		Rows(goqu.Record{"first_name": "bob", "last_name": "yukon"}).
   602  		ToSQL()
   603  	fmt.Println(insertSQL)
   604  	// Output:
   605  	// INSERT INTO "test" AS "t" ("first_name", "last_name") VALUES ('bob', 'yukon')
   606  }
   607  
   608  func ExampleInsertDataset_Cols() {
   609  	insertSQL, _, _ := goqu.Insert("test").
   610  		Cols("a", "b", "c").
   611  		Vals(
   612  			[]interface{}{"a1", "b1", "c1"},
   613  			[]interface{}{"a2", "b1", "c1"},
   614  			[]interface{}{"a3", "b1", "c1"},
   615  		).
   616  		ToSQL()
   617  	fmt.Println(insertSQL)
   618  	// Output:
   619  	// INSERT INTO "test" ("a", "b", "c") VALUES ('a1', 'b1', 'c1'), ('a2', 'b1', 'c1'), ('a3', 'b1', 'c1')
   620  }
   621  
   622  func ExampleInsertDataset_Cols_withFromQuery() {
   623  	insertSQL, _, _ := goqu.Insert("test").
   624  		Cols("a", "b", "c").
   625  		FromQuery(goqu.From("foo").Select("d", "e", "f")).
   626  		ToSQL()
   627  	fmt.Println(insertSQL)
   628  	// Output:
   629  	// INSERT INTO "test" ("a", "b", "c") SELECT "d", "e", "f" FROM "foo"
   630  }
   631  
   632  func ExampleInsertDataset_ColsAppend() {
   633  	insertSQL, _, _ := goqu.Insert("test").
   634  		Cols("a", "b").
   635  		ColsAppend("c").
   636  		Vals(
   637  			[]interface{}{"a1", "b1", "c1"},
   638  			[]interface{}{"a2", "b1", "c1"},
   639  			[]interface{}{"a3", "b1", "c1"},
   640  		).
   641  		ToSQL()
   642  	fmt.Println(insertSQL)
   643  	// Output:
   644  	// INSERT INTO "test" ("a", "b", "c") VALUES ('a1', 'b1', 'c1'), ('a2', 'b1', 'c1'), ('a3', 'b1', 'c1')
   645  }
   646  
   647  func ExampleInsertDataset_ClearCols() {
   648  	ds := goqu.Insert("test").Cols("a", "b", "c")
   649  	insertSQL, _, _ := ds.ClearCols().Cols("other_a", "other_b", "other_c").
   650  		FromQuery(goqu.From("foo").Select("d", "e", "f")).
   651  		ToSQL()
   652  	fmt.Println(insertSQL)
   653  	// Output:
   654  	// INSERT INTO "test" ("other_a", "other_b", "other_c") SELECT "d", "e", "f" FROM "foo"
   655  }
   656  
   657  func ExampleInsertDataset_Vals() {
   658  	insertSQL, _, _ := goqu.Insert("test").
   659  		Cols("a", "b", "c").
   660  		Vals(
   661  			[]interface{}{"a1", "b1", "c1"},
   662  			[]interface{}{"a2", "b2", "c2"},
   663  			[]interface{}{"a3", "b3", "c3"},
   664  		).
   665  		ToSQL()
   666  	fmt.Println(insertSQL)
   667  
   668  	insertSQL, _, _ = goqu.Insert("test").
   669  		Cols("a", "b", "c").
   670  		Vals([]interface{}{"a1", "b1", "c1"}).
   671  		Vals([]interface{}{"a2", "b2", "c2"}).
   672  		Vals([]interface{}{"a3", "b3", "c3"}).
   673  		ToSQL()
   674  	fmt.Println(insertSQL)
   675  
   676  	// Output:
   677  	// INSERT INTO "test" ("a", "b", "c") VALUES ('a1', 'b1', 'c1'), ('a2', 'b2', 'c2'), ('a3', 'b3', 'c3')
   678  	// INSERT INTO "test" ("a", "b", "c") VALUES ('a1', 'b1', 'c1'), ('a2', 'b2', 'c2'), ('a3', 'b3', 'c3')
   679  }
   680  
   681  func ExampleInsertDataset_ClearVals() {
   682  	insertSQL, _, _ := goqu.Insert("test").
   683  		Cols("a", "b", "c").
   684  		Vals(
   685  			[]interface{}{"a1", "b1", "c1"},
   686  			[]interface{}{"a2", "b1", "c1"},
   687  			[]interface{}{"a3", "b1", "c1"},
   688  		).
   689  		ClearVals().
   690  		ToSQL()
   691  	fmt.Println(insertSQL)
   692  
   693  	insertSQL, _, _ = goqu.Insert("test").
   694  		Cols("a", "b", "c").
   695  		Vals([]interface{}{"a1", "b1", "c1"}).
   696  		Vals([]interface{}{"a2", "b2", "c2"}).
   697  		Vals([]interface{}{"a3", "b3", "c3"}).
   698  		ClearVals().
   699  		ToSQL()
   700  	fmt.Println(insertSQL)
   701  	// Output:
   702  	// INSERT INTO "test" DEFAULT VALUES
   703  	// INSERT INTO "test" DEFAULT VALUES
   704  }
   705
View as plain text