Source file src/github.com/doug-martin/goqu/v9/expressions_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  	"fmt"
     6  	"regexp"
     7  
     8  	"github.com/doug-martin/goqu/v9"
     9  	"github.com/doug-martin/goqu/v9/exp"
    10  )
    11  
    12  func ExampleAVG() {
    13  	ds := goqu.From("test").Select(goqu.AVG("col"))
    14  	sql, args, _ := ds.ToSQL()
    15  	fmt.Println(sql, args)
    16  
    17  	sql, args, _ = ds.Prepared(true).ToSQL()
    18  	fmt.Println(sql, args)
    19  	// Output:
    20  	// SELECT AVG("col") FROM "test" []
    21  	// SELECT AVG("col") FROM "test" []
    22  }
    23  
    24  func ExampleAVG_as() {
    25  	sql, _, _ := goqu.From("test").Select(goqu.AVG("a").As("a")).ToSQL()
    26  	fmt.Println(sql)
    27  
    28  	// Output:
    29  	// SELECT AVG("a") AS "a" FROM "test"
    30  }
    31  
    32  func ExampleAVG_havingClause() {
    33  	ds := goqu.
    34  		From("test").
    35  		Select(goqu.AVG("a").As("avg")).
    36  		GroupBy("a").
    37  		Having(goqu.AVG("a").Gt(10))
    38  
    39  	sql, args, _ := ds.ToSQL()
    40  	fmt.Println(sql, args)
    41  
    42  	sql, args, _ = ds.Prepared(true).ToSQL()
    43  	fmt.Println(sql, args)
    44  
    45  	// Output:
    46  	// SELECT AVG("a") AS "avg" FROM "test" GROUP BY "a" HAVING (AVG("a") > 10) []
    47  	// SELECT AVG("a") AS "avg" FROM "test" GROUP BY "a" HAVING (AVG("a") > ?) [10]
    48  }
    49  
    50  func ExampleAnd() {
    51  	ds := goqu.From("test").Where(
    52  		goqu.And(
    53  			goqu.C("col").Gt(10),
    54  			goqu.C("col").Lt(20),
    55  		),
    56  	)
    57  	sql, args, _ := ds.ToSQL()
    58  	fmt.Println(sql, args)
    59  
    60  	sql, args, _ = ds.Prepared(true).ToSQL()
    61  	fmt.Println(sql, args)
    62  
    63  	// Output:
    64  	// SELECT * FROM "test" WHERE (("col" > 10) AND ("col" < 20)) []
    65  	// SELECT * FROM "test" WHERE (("col" > ?) AND ("col" < ?)) [10 20]
    66  }
    67  
    68  // You can use And with Or to create more complex queries
    69  func ExampleAnd_withOr() {
    70  	ds := goqu.From("test").Where(
    71  		goqu.And(
    72  			goqu.C("col1").IsTrue(),
    73  			goqu.Or(
    74  				goqu.C("col2").Gt(10),
    75  				goqu.C("col2").Lt(20),
    76  			),
    77  		),
    78  	)
    79  	sql, args, _ := ds.ToSQL()
    80  	fmt.Println(sql, args)
    81  
    82  	sql, args, _ = ds.Prepared(true).ToSQL()
    83  	fmt.Println(sql, args)
    84  
    85  	// by default expressions are anded together
    86  	ds = goqu.From("test").Where(
    87  		goqu.C("col1").IsTrue(),
    88  		goqu.Or(
    89  			goqu.C("col2").Gt(10),
    90  			goqu.C("col2").Lt(20),
    91  		),
    92  	)
    93  	sql, args, _ = ds.ToSQL()
    94  	fmt.Println(sql, args)
    95  
    96  	sql, args, _ = ds.Prepared(true).ToSQL()
    97  	fmt.Println(sql, args)
    98  
    99  	// Output:
   100  	// SELECT * FROM "test" WHERE (("col1" IS TRUE) AND (("col2" > 10) OR ("col2" < 20))) []
   101  	// SELECT * FROM "test" WHERE (("col1" IS TRUE) AND (("col2" > ?) OR ("col2" < ?))) [10 20]
   102  	// SELECT * FROM "test" WHERE (("col1" IS TRUE) AND (("col2" > 10) OR ("col2" < 20))) []
   103  	// SELECT * FROM "test" WHERE (("col1" IS TRUE) AND (("col2" > ?) OR ("col2" < ?))) [10 20]
   104  }
   105  
   106  // You can use ExOr inside of And expression lists.
   107  func ExampleAnd_withExOr() {
   108  	// by default expressions are anded together
   109  	ds := goqu.From("test").Where(
   110  		goqu.C("col1").IsTrue(),
   111  		goqu.ExOr{
   112  			"col2": goqu.Op{"gt": 10},
   113  			"col3": goqu.Op{"lt": 20},
   114  		},
   115  	)
   116  	sql, args, _ := ds.ToSQL()
   117  	fmt.Println(sql, args)
   118  
   119  	sql, args, _ = ds.Prepared(true).ToSQL()
   120  	fmt.Println(sql, args)
   121  
   122  	// Output:
   123  	// SELECT * FROM "test" WHERE (("col1" IS TRUE) AND (("col2" > 10) OR ("col3" < 20))) []
   124  	// SELECT * FROM "test" WHERE (("col1" IS TRUE) AND (("col2" > ?) OR ("col3" < ?))) [10 20]
   125  }
   126  
   127  func ExampleC() {
   128  	sql, args, _ := goqu.From("test").
   129  		Select(goqu.C("*")).
   130  		ToSQL()
   131  	fmt.Println(sql, args)
   132  
   133  	sql, args, _ = goqu.From("test").
   134  		Select(goqu.C("col1")).
   135  		ToSQL()
   136  	fmt.Println(sql, args)
   137  
   138  	ds := goqu.From("test").Where(
   139  		goqu.C("col1").Eq(10),
   140  		goqu.C("col2").In([]int64{1, 2, 3, 4}),
   141  		goqu.C("col3").Like(regexp.MustCompile("^[ab]")),
   142  		goqu.C("col4").IsNull(),
   143  	)
   144  
   145  	sql, args, _ = ds.ToSQL()
   146  	fmt.Println(sql, args)
   147  
   148  	sql, args, _ = ds.Prepared(true).ToSQL()
   149  	fmt.Println(sql, args)
   150  
   151  	// Output:
   152  	// SELECT * FROM "test" []
   153  	// SELECT "col1" FROM "test" []
   154  	// SELECT * FROM "test" WHERE (("col1" = 10) AND ("col2" IN (1, 2, 3, 4)) AND ("col3" ~ '^[ab]') AND ("col4" IS NULL)) []
   155  	// SELECT * FROM "test" WHERE (("col1" = ?) AND ("col2" IN (?, ?, ?, ?)) AND ("col3" ~ ?) AND ("col4" IS NULL)) [10 1 2 3 4 ^[ab]]
   156  }
   157  
   158  func ExampleC_as() {
   159  	sql, _, _ := goqu.From("test").Select(goqu.C("a").As("as_a")).ToSQL()
   160  	fmt.Println(sql)
   161  
   162  	sql, _, _ = goqu.From("test").Select(goqu.C("a").As(goqu.C("as_a"))).ToSQL()
   163  	fmt.Println(sql)
   164  
   165  	// Output:
   166  	// SELECT "a" AS "as_a" FROM "test"
   167  	// SELECT "a" AS "as_a" FROM "test"
   168  }
   169  
   170  func ExampleC_ordering() {
   171  	sql, args, _ := goqu.From("test").Order(goqu.C("a").Asc()).ToSQL()
   172  	fmt.Println(sql, args)
   173  
   174  	sql, args, _ = goqu.From("test").Order(goqu.C("a").Asc().NullsFirst()).ToSQL()
   175  	fmt.Println(sql, args)
   176  
   177  	sql, args, _ = goqu.From("test").Order(goqu.C("a").Asc().NullsLast()).ToSQL()
   178  	fmt.Println(sql, args)
   179  
   180  	sql, args, _ = goqu.From("test").Order(goqu.C("a").Desc()).ToSQL()
   181  	fmt.Println(sql, args)
   182  
   183  	sql, args, _ = goqu.From("test").Order(goqu.C("a").Desc().NullsFirst()).ToSQL()
   184  	fmt.Println(sql, args)
   185  
   186  	sql, args, _ = goqu.From("test").Order(goqu.C("a").Desc().NullsLast()).ToSQL()
   187  	fmt.Println(sql, args)
   188  
   189  	// Output:
   190  	// SELECT * FROM "test" ORDER BY "a" ASC []
   191  	// SELECT * FROM "test" ORDER BY "a" ASC NULLS FIRST []
   192  	// SELECT * FROM "test" ORDER BY "a" ASC NULLS LAST []
   193  	// SELECT * FROM "test" ORDER BY "a" DESC []
   194  	// SELECT * FROM "test" ORDER BY "a" DESC NULLS FIRST []
   195  	// SELECT * FROM "test" ORDER BY "a" DESC NULLS LAST []
   196  }
   197  
   198  func ExampleC_cast() {
   199  	sql, _, _ := goqu.From("test").
   200  		Select(goqu.C("json1").Cast("TEXT").As("json_text")).
   201  		ToSQL()
   202  	fmt.Println(sql)
   203  
   204  	sql, _, _ = goqu.From("test").Where(
   205  		goqu.C("json1").Cast("TEXT").Neq(
   206  			goqu.C("json2").Cast("TEXT"),
   207  		),
   208  	).ToSQL()
   209  	fmt.Println(sql)
   210  	// Output:
   211  	// SELECT CAST("json1" AS TEXT) AS "json_text" FROM "test"
   212  	// SELECT * FROM "test" WHERE (CAST("json1" AS TEXT) != CAST("json2" AS TEXT))
   213  }
   214  
   215  func ExampleC_comparisons() {
   216  	// used from an identifier
   217  	sql, _, _ := goqu.From("test").Where(goqu.C("a").Eq(10)).ToSQL()
   218  	fmt.Println(sql)
   219  
   220  	sql, _, _ = goqu.From("test").Where(goqu.C("a").Neq(10)).ToSQL()
   221  	fmt.Println(sql)
   222  
   223  	sql, _, _ = goqu.From("test").Where(goqu.C("a").Gt(10)).ToSQL()
   224  	fmt.Println(sql)
   225  
   226  	sql, _, _ = goqu.From("test").Where(goqu.C("a").Gte(10)).ToSQL()
   227  	fmt.Println(sql)
   228  
   229  	sql, _, _ = goqu.From("test").Where(goqu.C("a").Lt(10)).ToSQL()
   230  	fmt.Println(sql)
   231  
   232  	sql, _, _ = goqu.From("test").Where(goqu.C("a").Lte(10)).ToSQL()
   233  	fmt.Println(sql)
   234  
   235  	// Output:
   236  	// SELECT * FROM "test" WHERE ("a" = 10)
   237  	// SELECT * FROM "test" WHERE ("a" != 10)
   238  	// SELECT * FROM "test" WHERE ("a" > 10)
   239  	// SELECT * FROM "test" WHERE ("a" >= 10)
   240  	// SELECT * FROM "test" WHERE ("a" < 10)
   241  	// SELECT * FROM "test" WHERE ("a" <= 10)
   242  }
   243  
   244  func ExampleC_inOperators() {
   245  	// using identifiers
   246  	sql, _, _ := goqu.From("test").Where(goqu.C("a").In("a", "b", "c")).ToSQL()
   247  	fmt.Println(sql)
   248  	// with a slice
   249  	sql, _, _ = goqu.From("test").Where(goqu.C("a").In([]string{"a", "b", "c"})).ToSQL()
   250  	fmt.Println(sql)
   251  
   252  	sql, _, _ = goqu.From("test").Where(goqu.C("a").NotIn("a", "b", "c")).ToSQL()
   253  	fmt.Println(sql)
   254  	// with a slice
   255  	sql, _, _ = goqu.From("test").Where(goqu.C("a").NotIn([]string{"a", "b", "c"})).ToSQL()
   256  	fmt.Println(sql)
   257  
   258  	// Output:
   259  	// SELECT * FROM "test" WHERE ("a" IN ('a', 'b', 'c'))
   260  	// SELECT * FROM "test" WHERE ("a" IN ('a', 'b', 'c'))
   261  	// SELECT * FROM "test" WHERE ("a" NOT IN ('a', 'b', 'c'))
   262  	// SELECT * FROM "test" WHERE ("a" NOT IN ('a', 'b', 'c'))
   263  }
   264  
   265  func ExampleC_likeComparisons() {
   266  	// using identifiers
   267  	sql, _, _ := goqu.From("test").Where(goqu.C("a").Like("%a%")).ToSQL()
   268  	fmt.Println(sql)
   269  
   270  	sql, _, _ = goqu.From("test").Where(goqu.C("a").Like(regexp.MustCompile(`[ab]`))).ToSQL()
   271  	fmt.Println(sql)
   272  
   273  	sql, _, _ = goqu.From("test").Where(goqu.C("a").ILike("%a%")).ToSQL()
   274  	fmt.Println(sql)
   275  
   276  	sql, _, _ = goqu.From("test").Where(goqu.C("a").ILike(regexp.MustCompile("[ab]"))).ToSQL()
   277  	fmt.Println(sql)
   278  
   279  	sql, _, _ = goqu.From("test").Where(goqu.C("a").NotLike("%a%")).ToSQL()
   280  	fmt.Println(sql)
   281  
   282  	sql, _, _ = goqu.From("test").Where(goqu.C("a").NotLike(regexp.MustCompile("[ab]"))).ToSQL()
   283  	fmt.Println(sql)
   284  
   285  	sql, _, _ = goqu.From("test").Where(goqu.C("a").NotILike("%a%")).ToSQL()
   286  	fmt.Println(sql)
   287  
   288  	sql, _, _ = goqu.From("test").Where(goqu.C("a").NotILike(regexp.MustCompile(`[ab]`))).ToSQL()
   289  	fmt.Println(sql)
   290  
   291  	// Output:
   292  	// SELECT * FROM "test" WHERE ("a" LIKE '%a%')
   293  	// SELECT * FROM "test" WHERE ("a" ~ '[ab]')
   294  	// SELECT * FROM "test" WHERE ("a" ILIKE '%a%')
   295  	// SELECT * FROM "test" WHERE ("a" ~* '[ab]')
   296  	// SELECT * FROM "test" WHERE ("a" NOT LIKE '%a%')
   297  	// SELECT * FROM "test" WHERE ("a" !~ '[ab]')
   298  	// SELECT * FROM "test" WHERE ("a" NOT ILIKE '%a%')
   299  	// SELECT * FROM "test" WHERE ("a" !~* '[ab]')
   300  }
   301  
   302  func ExampleC_isComparisons() {
   303  	sql, args, _ := goqu.From("test").Where(goqu.C("a").Is(nil)).ToSQL()
   304  	fmt.Println(sql, args)
   305  
   306  	sql, args, _ = goqu.From("test").Where(goqu.C("a").Is(true)).ToSQL()
   307  	fmt.Println(sql, args)
   308  
   309  	sql, args, _ = goqu.From("test").Where(goqu.C("a").Is(false)).ToSQL()
   310  	fmt.Println(sql, args)
   311  
   312  	sql, args, _ = goqu.From("test").Where(goqu.C("a").IsNull()).ToSQL()
   313  	fmt.Println(sql, args)
   314  
   315  	sql, args, _ = goqu.From("test").Where(goqu.C("a").IsTrue()).ToSQL()
   316  	fmt.Println(sql, args)
   317  
   318  	sql, args, _ = goqu.From("test").Where(goqu.C("a").IsFalse()).ToSQL()
   319  	fmt.Println(sql, args)
   320  
   321  	sql, args, _ = goqu.From("test").Where(goqu.C("a").IsNot(nil)).ToSQL()
   322  	fmt.Println(sql, args)
   323  
   324  	sql, args, _ = goqu.From("test").Where(goqu.C("a").IsNot(true)).ToSQL()
   325  	fmt.Println(sql, args)
   326  
   327  	sql, args, _ = goqu.From("test").Where(goqu.C("a").IsNot(false)).ToSQL()
   328  	fmt.Println(sql, args)
   329  
   330  	sql, args, _ = goqu.From("test").Where(goqu.C("a").IsNotNull()).ToSQL()
   331  	fmt.Println(sql, args)
   332  
   333  	sql, args, _ = goqu.From("test").Where(goqu.C("a").IsNotTrue()).ToSQL()
   334  	fmt.Println(sql, args)
   335  
   336  	sql, args, _ = goqu.From("test").Where(goqu.C("a").IsNotFalse()).ToSQL()
   337  	fmt.Println(sql, args)
   338  
   339  	// Output:
   340  	// SELECT * FROM "test" WHERE ("a" IS NULL) []
   341  	// SELECT * FROM "test" WHERE ("a" IS TRUE) []
   342  	// SELECT * FROM "test" WHERE ("a" IS FALSE) []
   343  	// SELECT * FROM "test" WHERE ("a" IS NULL) []
   344  	// SELECT * FROM "test" WHERE ("a" IS TRUE) []
   345  	// SELECT * FROM "test" WHERE ("a" IS FALSE) []
   346  	// SELECT * FROM "test" WHERE ("a" IS NOT NULL) []
   347  	// SELECT * FROM "test" WHERE ("a" IS NOT TRUE) []
   348  	// SELECT * FROM "test" WHERE ("a" IS NOT FALSE) []
   349  	// SELECT * FROM "test" WHERE ("a" IS NOT NULL) []
   350  	// SELECT * FROM "test" WHERE ("a" IS NOT TRUE) []
   351  	// SELECT * FROM "test" WHERE ("a" IS NOT FALSE) []
   352  }
   353  
   354  func ExampleC_betweenComparisons() {
   355  	ds := goqu.From("test").Where(
   356  		goqu.C("a").Between(goqu.Range(1, 10)),
   357  	)
   358  	sql, args, _ := ds.ToSQL()
   359  	fmt.Println(sql, args)
   360  
   361  	sql, args, _ = ds.Prepared(true).ToSQL()
   362  	fmt.Println(sql, args)
   363  
   364  	ds = goqu.From("test").Where(
   365  		goqu.C("a").NotBetween(goqu.Range(1, 10)),
   366  	)
   367  	sql, args, _ = ds.ToSQL()
   368  	fmt.Println(sql, args)
   369  
   370  	sql, args, _ = ds.Prepared(true).ToSQL()
   371  	fmt.Println(sql, args)
   372  
   373  	// Output:
   374  	// SELECT * FROM "test" WHERE ("a" BETWEEN 1 AND 10) []
   375  	// SELECT * FROM "test" WHERE ("a" BETWEEN ? AND ?) [1 10]
   376  	// SELECT * FROM "test" WHERE ("a" NOT BETWEEN 1 AND 10) []
   377  	// SELECT * FROM "test" WHERE ("a" NOT BETWEEN ? AND ?) [1 10]
   378  }
   379  
   380  func ExampleCOALESCE() {
   381  	ds := goqu.From("test").Select(
   382  		goqu.COALESCE(goqu.C("a"), "a"),
   383  		goqu.COALESCE(goqu.C("a"), goqu.C("b"), nil),
   384  	)
   385  	sql, args, _ := ds.ToSQL()
   386  	fmt.Println(sql, args)
   387  
   388  	sql, args, _ = ds.Prepared(true).ToSQL()
   389  	fmt.Println(sql, args)
   390  	// Output:
   391  	// SELECT COALESCE("a", 'a'), COALESCE("a", "b", NULL) FROM "test" []
   392  	// SELECT COALESCE("a", ?), COALESCE("a", "b", ?) FROM "test" [a <nil>]
   393  }
   394  
   395  func ExampleCOALESCE_as() {
   396  	sql, _, _ := goqu.From("test").Select(goqu.COALESCE(goqu.C("a"), "a").As("a")).ToSQL()
   397  	fmt.Println(sql)
   398  
   399  	// Output:
   400  	// SELECT COALESCE("a", 'a') AS "a" FROM "test"
   401  }
   402  
   403  func ExampleCOUNT() {
   404  	ds := goqu.From("test").Select(goqu.COUNT("*"))
   405  	sql, args, _ := ds.ToSQL()
   406  	fmt.Println(sql, args)
   407  
   408  	sql, args, _ = ds.Prepared(true).ToSQL()
   409  	fmt.Println(sql, args)
   410  	// Output:
   411  	// SELECT COUNT(*) FROM "test" []
   412  	// SELECT COUNT(*) FROM "test" []
   413  }
   414  
   415  func ExampleCOUNT_as() {
   416  	sql, _, _ := goqu.From("test").Select(goqu.COUNT("*").As("count")).ToSQL()
   417  	fmt.Println(sql)
   418  
   419  	// Output:
   420  	// SELECT COUNT(*) AS "count" FROM "test"
   421  }
   422  
   423  func ExampleCOUNT_havingClause() {
   424  	ds := goqu.
   425  		From("test").
   426  		Select(goqu.COUNT("a").As("COUNT")).
   427  		GroupBy("a").
   428  		Having(goqu.COUNT("a").Gt(10))
   429  
   430  	sql, args, _ := ds.ToSQL()
   431  	fmt.Println(sql, args)
   432  
   433  	sql, args, _ = ds.Prepared(true).ToSQL()
   434  	fmt.Println(sql, args)
   435  
   436  	// Output:
   437  	// SELECT COUNT("a") AS "COUNT" FROM "test" GROUP BY "a" HAVING (COUNT("a") > 10) []
   438  	// SELECT COUNT("a") AS "COUNT" FROM "test" GROUP BY "a" HAVING (COUNT("a") > ?) [10]
   439  }
   440  
   441  func ExampleCast() {
   442  	sql, _, _ := goqu.From("test").
   443  		Select(goqu.Cast(goqu.C("json1"), "TEXT").As("json_text")).
   444  		ToSQL()
   445  	fmt.Println(sql)
   446  
   447  	sql, _, _ = goqu.From("test").Where(
   448  		goqu.Cast(goqu.C("json1"), "TEXT").Neq(
   449  			goqu.Cast(goqu.C("json2"), "TEXT"),
   450  		),
   451  	).ToSQL()
   452  	fmt.Println(sql)
   453  	// Output:
   454  	// SELECT CAST("json1" AS TEXT) AS "json_text" FROM "test"
   455  	// SELECT * FROM "test" WHERE (CAST("json1" AS TEXT) != CAST("json2" AS TEXT))
   456  }
   457  
   458  func ExampleDISTINCT() {
   459  	ds := goqu.From("test").Select(goqu.DISTINCT("col"))
   460  	sql, args, _ := ds.ToSQL()
   461  	fmt.Println(sql, args)
   462  
   463  	sql, args, _ = ds.Prepared(true).ToSQL()
   464  	fmt.Println(sql, args)
   465  	// Output:
   466  	// SELECT DISTINCT("col") FROM "test" []
   467  	// SELECT DISTINCT("col") FROM "test" []
   468  }
   469  
   470  func ExampleDISTINCT_as() {
   471  	sql, _, _ := goqu.From("test").Select(goqu.DISTINCT("a").As("distinct_a")).ToSQL()
   472  	fmt.Println(sql)
   473  
   474  	// Output:
   475  	// SELECT DISTINCT("a") AS "distinct_a" FROM "test"
   476  }
   477  
   478  func ExampleDefault() {
   479  	ds := goqu.Insert("items")
   480  
   481  	sql, args, _ := ds.Rows(goqu.Record{
   482  		"name":    goqu.Default(),
   483  		"address": goqu.Default(),
   484  	}).ToSQL()
   485  	fmt.Println(sql, args)
   486  
   487  	sql, args, _ = ds.Prepared(true).Rows(goqu.Record{
   488  		"name":    goqu.Default(),
   489  		"address": goqu.Default(),
   490  	}).ToSQL()
   491  	fmt.Println(sql, args)
   492  
   493  	// Output:
   494  	// INSERT INTO "items" ("address", "name") VALUES (DEFAULT, DEFAULT) []
   495  	// INSERT INTO "items" ("address", "name") VALUES (DEFAULT, DEFAULT) []
   496  }
   497  
   498  func ExampleDoNothing() {
   499  	ds := goqu.Insert("items")
   500  
   501  	sql, args, _ := ds.Rows(goqu.Record{
   502  		"address": "111 Address",
   503  		"name":    "bob",
   504  	}).OnConflict(goqu.DoNothing()).ToSQL()
   505  	fmt.Println(sql, args)
   506  
   507  	sql, args, _ = ds.Prepared(true).Rows(goqu.Record{
   508  		"address": "111 Address",
   509  		"name":    "bob",
   510  	}).OnConflict(goqu.DoNothing()).ToSQL()
   511  	fmt.Println(sql, args)
   512  
   513  	// Output:
   514  	// INSERT INTO "items" ("address", "name") VALUES ('111 Address', 'bob') ON CONFLICT DO NOTHING []
   515  	// INSERT INTO "items" ("address", "name") VALUES (?, ?) ON CONFLICT DO NOTHING [111 Address bob]
   516  }
   517  
   518  func ExampleDoUpdate() {
   519  	ds := goqu.Insert("items")
   520  
   521  	sql, args, _ := ds.
   522  		Rows(goqu.Record{"address": "111 Address"}).
   523  		OnConflict(goqu.DoUpdate("address", goqu.C("address").Set(goqu.I("excluded.address")))).
   524  		ToSQL()
   525  	fmt.Println(sql, args)
   526  
   527  	sql, args, _ = ds.Prepared(true).
   528  		Rows(goqu.Record{"address": "111 Address"}).
   529  		OnConflict(goqu.DoUpdate("address", goqu.C("address").Set(goqu.I("excluded.address")))).
   530  		ToSQL()
   531  	fmt.Println(sql, args)
   532  
   533  	// Output:
   534  	// INSERT INTO "items" ("address") VALUES ('111 Address') ON CONFLICT (address) DO UPDATE SET "address"="excluded"."address" []
   535  	// INSERT INTO "items" ("address") VALUES (?) ON CONFLICT (address) DO UPDATE SET "address"="excluded"."address" [111 Address]
   536  }
   537  
   538  func ExampleDoUpdate_where() {
   539  	ds := goqu.Insert("items")
   540  
   541  	sql, args, _ := ds.
   542  		Rows(goqu.Record{"address": "111 Address"}).
   543  		OnConflict(goqu.DoUpdate(
   544  			"address",
   545  			goqu.C("address").Set(goqu.I("excluded.address"))).Where(goqu.I("items.updated").IsNull()),
   546  		).
   547  		ToSQL()
   548  	fmt.Println(sql, args)
   549  
   550  	sql, args, _ = ds.Prepared(true).
   551  		Rows(goqu.Record{"address": "111 Address"}).
   552  		OnConflict(goqu.DoUpdate(
   553  			"address",
   554  			goqu.C("address").Set(goqu.I("excluded.address"))).Where(goqu.I("items.updated").IsNull()),
   555  		).
   556  		ToSQL()
   557  	fmt.Println(sql, args)
   558  
   559  	// Output:
   560  	// INSERT INTO "items" ("address") VALUES ('111 Address') ON CONFLICT (address) DO UPDATE SET "address"="excluded"."address" WHERE ("items"."updated" IS NULL) []
   561  	// INSERT INTO "items" ("address") VALUES (?) ON CONFLICT (address) DO UPDATE SET "address"="excluded"."address" WHERE ("items"."updated" IS NULL) [111 Address]
   562  }
   563  
   564  func ExampleFIRST() {
   565  	ds := goqu.From("test").Select(goqu.FIRST("col"))
   566  	sql, args, _ := ds.ToSQL()
   567  	fmt.Println(sql, args)
   568  
   569  	sql, args, _ = ds.Prepared(true).ToSQL()
   570  	fmt.Println(sql, args)
   571  	// Output:
   572  	// SELECT FIRST("col") FROM "test" []
   573  	// SELECT FIRST("col") FROM "test" []
   574  }
   575  
   576  func ExampleFIRST_as() {
   577  	sql, _, _ := goqu.From("test").Select(goqu.FIRST("a").As("a")).ToSQL()
   578  	fmt.Println(sql)
   579  
   580  	// Output:
   581  	// SELECT FIRST("a") AS "a" FROM "test"
   582  }
   583  
   584  // This example shows how to create custom SQL Functions
   585  func ExampleFunc() {
   586  	stragg := func(expression exp.Expression, delimiter string) exp.SQLFunctionExpression {
   587  		return goqu.Func("str_agg", expression, goqu.L(delimiter))
   588  	}
   589  	sql, _, _ := goqu.From("test").Select(stragg(goqu.C("col"), "|")).ToSQL()
   590  	fmt.Println(sql)
   591  
   592  	// Output:
   593  	// SELECT str_agg("col", |) FROM "test"
   594  }
   595  
   596  func ExampleI() {
   597  	ds := goqu.From("test").
   598  		Select(
   599  			goqu.I("my_schema.table.col1"),
   600  			goqu.I("table.col2"),
   601  			goqu.I("col3"),
   602  		)
   603  
   604  	sql, args, _ := ds.ToSQL()
   605  	fmt.Println(sql, args)
   606  
   607  	sql, args, _ = ds.Prepared(true).ToSQL()
   608  	fmt.Println(sql, args)
   609  
   610  	ds = goqu.From("test").Select(goqu.I("test.*"))
   611  
   612  	sql, args, _ = ds.ToSQL()
   613  	fmt.Println(sql, args)
   614  
   615  	sql, args, _ = ds.Prepared(true).ToSQL()
   616  	fmt.Println(sql, args)
   617  
   618  	// Output:
   619  	// SELECT "my_schema"."table"."col1", "table"."col2", "col3" FROM "test" []
   620  	// SELECT "my_schema"."table"."col1", "table"."col2", "col3" FROM "test" []
   621  	// SELECT "test".* FROM "test" []
   622  	// SELECT "test".* FROM "test" []
   623  }
   624  
   625  func ExampleL() {
   626  	ds := goqu.From("test").Where(
   627  		// literal with no args
   628  		goqu.L(`"col"::TEXT = ""other_col"::text`),
   629  		// literal with args they will be interpolated into the sql by default
   630  		goqu.L("col IN (?, ?, ?)", "a", "b", "c"),
   631  	)
   632  
   633  	sql, args, _ := ds.ToSQL()
   634  	fmt.Println(sql, args)
   635  
   636  	sql, args, _ = ds.Prepared(true).ToSQL()
   637  	fmt.Println(sql, args)
   638  	// Output:
   639  	// SELECT * FROM "test" WHERE ("col"::TEXT = ""other_col"::text AND col IN ('a', 'b', 'c')) []
   640  	// SELECT * FROM "test" WHERE ("col"::TEXT = ""other_col"::text AND col IN (?, ?, ?)) [a b c]
   641  }
   642  
   643  func ExampleL_withArgs() {
   644  	ds := goqu.From("test").Where(
   645  		goqu.L(
   646  			"(? AND ?) OR ?",
   647  			goqu.C("a").Eq(1),
   648  			goqu.C("b").Eq("b"),
   649  			goqu.C("c").In([]string{"a", "b", "c"}),
   650  		),
   651  	)
   652  
   653  	sql, args, _ := ds.ToSQL()
   654  	fmt.Println(sql, args)
   655  
   656  	sql, args, _ = ds.Prepared(true).ToSQL()
   657  	fmt.Println(sql, args)
   658  	// Output:
   659  	// SELECT * FROM "test" WHERE (("a" = 1) AND ("b" = 'b')) OR ("c" IN ('a', 'b', 'c')) []
   660  	// SELECT * FROM "test" WHERE (("a" = ?) AND ("b" = ?)) OR ("c" IN (?, ?, ?)) [1 b a b c]
   661  }
   662  
   663  func ExampleL_as() {
   664  	sql, _, _ := goqu.From("test").Select(goqu.L("json_col->>'totalAmount'").As("total_amount")).ToSQL()
   665  	fmt.Println(sql)
   666  
   667  	// Output:
   668  	// SELECT json_col->>'totalAmount' AS "total_amount" FROM "test"
   669  }
   670  
   671  func ExampleL_comparisons() {
   672  	// used from a literal expression
   673  	sql, _, _ := goqu.From("test").Where(goqu.L("(a + b)").Eq(10)).ToSQL()
   674  	fmt.Println(sql)
   675  
   676  	sql, _, _ = goqu.From("test").Where(goqu.L("(a + b)").Neq(10)).ToSQL()
   677  	fmt.Println(sql)
   678  
   679  	sql, _, _ = goqu.From("test").Where(goqu.L("(a + b)").Gt(10)).ToSQL()
   680  	fmt.Println(sql)
   681  
   682  	sql, _, _ = goqu.From("test").Where(goqu.L("(a + b)").Gte(10)).ToSQL()
   683  	fmt.Println(sql)
   684  
   685  	sql, _, _ = goqu.From("test").Where(goqu.L("(a + b)").Lt(10)).ToSQL()
   686  	fmt.Println(sql)
   687  
   688  	sql, _, _ = goqu.From("test").Where(goqu.L("(a + b)").Lte(10)).ToSQL()
   689  	fmt.Println(sql)
   690  
   691  	// Output:
   692  	// SELECT * FROM "test" WHERE ((a + b) = 10)
   693  	// SELECT * FROM "test" WHERE ((a + b) != 10)
   694  	// SELECT * FROM "test" WHERE ((a + b) > 10)
   695  	// SELECT * FROM "test" WHERE ((a + b) >= 10)
   696  	// SELECT * FROM "test" WHERE ((a + b) < 10)
   697  	// SELECT * FROM "test" WHERE ((a + b) <= 10)
   698  }
   699  
   700  func ExampleL_inOperators() {
   701  	// using identifiers
   702  	sql, _, _ := goqu.From("test").Where(goqu.L("json_col->>'val'").In("a", "b", "c")).ToSQL()
   703  	fmt.Println(sql)
   704  	// with a slice
   705  	sql, _, _ = goqu.From("test").Where(goqu.L("json_col->>'val'").In([]string{"a", "b", "c"})).ToSQL()
   706  	fmt.Println(sql)
   707  
   708  	sql, _, _ = goqu.From("test").Where(goqu.L("json_col->>'val'").NotIn("a", "b", "c")).ToSQL()
   709  	fmt.Println(sql)
   710  	// with a slice
   711  	sql, _, _ = goqu.From("test").Where(goqu.L("json_col->>'val'").NotIn([]string{"a", "b", "c"})).ToSQL()
   712  	fmt.Println(sql)
   713  
   714  	// Output:
   715  	// SELECT * FROM "test" WHERE (json_col->>'val' IN ('a', 'b', 'c'))
   716  	// SELECT * FROM "test" WHERE (json_col->>'val' IN ('a', 'b', 'c'))
   717  	// SELECT * FROM "test" WHERE (json_col->>'val' NOT IN ('a', 'b', 'c'))
   718  	// SELECT * FROM "test" WHERE (json_col->>'val' NOT IN ('a', 'b', 'c'))
   719  }
   720  
   721  func ExampleL_likeComparisons() {
   722  	// using identifiers
   723  	sql, _, _ := goqu.From("test").Where(goqu.L("(a::text || 'bar')").Like("%a%")).ToSQL()
   724  	fmt.Println(sql)
   725  
   726  	sql, _, _ = goqu.From("test").Where(
   727  		goqu.L("(a::text || 'bar')").Like(regexp.MustCompile("[ab]")),
   728  	).ToSQL()
   729  	fmt.Println(sql)
   730  
   731  	sql, _, _ = goqu.From("test").Where(goqu.L("(a::text || 'bar')").ILike("%a%")).ToSQL()
   732  	fmt.Println(sql)
   733  
   734  	sql, _, _ = goqu.From("test").Where(
   735  		goqu.L("(a::text || 'bar')").ILike(regexp.MustCompile("[ab]")),
   736  	).ToSQL()
   737  	fmt.Println(sql)
   738  
   739  	sql, _, _ = goqu.From("test").Where(goqu.L("(a::text || 'bar')").NotLike("%a%")).ToSQL()
   740  	fmt.Println(sql)
   741  
   742  	sql, _, _ = goqu.From("test").Where(
   743  		goqu.L("(a::text || 'bar')").NotLike(regexp.MustCompile("[ab]")),
   744  	).ToSQL()
   745  	fmt.Println(sql)
   746  
   747  	sql, _, _ = goqu.From("test").Where(goqu.L("(a::text || 'bar')").NotILike("%a%")).ToSQL()
   748  	fmt.Println(sql)
   749  
   750  	sql, _, _ = goqu.From("test").Where(
   751  		goqu.L("(a::text || 'bar')").NotILike(regexp.MustCompile("[ab]")),
   752  	).ToSQL()
   753  	fmt.Println(sql)
   754  
   755  	// Output:
   756  	// SELECT * FROM "test" WHERE ((a::text || 'bar') LIKE '%a%')
   757  	// SELECT * FROM "test" WHERE ((a::text || 'bar') ~ '[ab]')
   758  	// SELECT * FROM "test" WHERE ((a::text || 'bar') ILIKE '%a%')
   759  	// SELECT * FROM "test" WHERE ((a::text || 'bar') ~* '[ab]')
   760  	// SELECT * FROM "test" WHERE ((a::text || 'bar') NOT LIKE '%a%')
   761  	// SELECT * FROM "test" WHERE ((a::text || 'bar') !~ '[ab]')
   762  	// SELECT * FROM "test" WHERE ((a::text || 'bar') NOT ILIKE '%a%')
   763  	// SELECT * FROM "test" WHERE ((a::text || 'bar') !~* '[ab]')
   764  }
   765  
   766  func ExampleL_isComparisons() {
   767  	sql, args, _ := goqu.From("test").Where(goqu.L("a").Is(nil)).ToSQL()
   768  	fmt.Println(sql, args)
   769  
   770  	sql, args, _ = goqu.From("test").Where(goqu.L("a").Is(true)).ToSQL()
   771  	fmt.Println(sql, args)
   772  
   773  	sql, args, _ = goqu.From("test").Where(goqu.L("a").Is(false)).ToSQL()
   774  	fmt.Println(sql, args)
   775  
   776  	sql, args, _ = goqu.From("test").Where(goqu.L("a").IsNull()).ToSQL()
   777  	fmt.Println(sql, args)
   778  
   779  	sql, args, _ = goqu.From("test").Where(goqu.L("a").IsTrue()).ToSQL()
   780  	fmt.Println(sql, args)
   781  
   782  	sql, args, _ = goqu.From("test").Where(goqu.L("a").IsFalse()).ToSQL()
   783  	fmt.Println(sql, args)
   784  
   785  	sql, args, _ = goqu.From("test").Where(goqu.L("a").IsNot(nil)).ToSQL()
   786  	fmt.Println(sql, args)
   787  
   788  	sql, args, _ = goqu.From("test").Where(goqu.L("a").IsNot(true)).ToSQL()
   789  	fmt.Println(sql, args)
   790  
   791  	sql, args, _ = goqu.From("test").Where(goqu.L("a").IsNot(false)).ToSQL()
   792  	fmt.Println(sql, args)
   793  
   794  	sql, args, _ = goqu.From("test").Where(goqu.L("a").IsNotNull()).ToSQL()
   795  	fmt.Println(sql, args)
   796  
   797  	sql, args, _ = goqu.From("test").Where(goqu.L("a").IsNotTrue()).ToSQL()
   798  	fmt.Println(sql, args)
   799  
   800  	sql, args, _ = goqu.From("test").Where(goqu.L("a").IsNotFalse()).ToSQL()
   801  	fmt.Println(sql, args)
   802  
   803  	// Output:
   804  	// SELECT * FROM "test" WHERE (a IS NULL) []
   805  	// SELECT * FROM "test" WHERE (a IS TRUE) []
   806  	// SELECT * FROM "test" WHERE (a IS FALSE) []
   807  	// SELECT * FROM "test" WHERE (a IS NULL) []
   808  	// SELECT * FROM "test" WHERE (a IS TRUE) []
   809  	// SELECT * FROM "test" WHERE (a IS FALSE) []
   810  	// SELECT * FROM "test" WHERE (a IS NOT NULL) []
   811  	// SELECT * FROM "test" WHERE (a IS NOT TRUE) []
   812  	// SELECT * FROM "test" WHERE (a IS NOT FALSE) []
   813  	// SELECT * FROM "test" WHERE (a IS NOT NULL) []
   814  	// SELECT * FROM "test" WHERE (a IS NOT TRUE) []
   815  	// SELECT * FROM "test" WHERE (a IS NOT FALSE) []
   816  }
   817  
   818  func ExampleL_betweenComparisons() {
   819  	ds := goqu.From("test").Where(
   820  		goqu.L("(a + b)").Between(goqu.Range(1, 10)),
   821  	)
   822  	sql, args, _ := ds.ToSQL()
   823  	fmt.Println(sql, args)
   824  
   825  	sql, args, _ = ds.Prepared(true).ToSQL()
   826  	fmt.Println(sql, args)
   827  
   828  	ds = goqu.From("test").Where(
   829  		goqu.L("(a + b)").NotBetween(goqu.Range(1, 10)),
   830  	)
   831  	sql, args, _ = ds.ToSQL()
   832  	fmt.Println(sql, args)
   833  
   834  	sql, args, _ = ds.Prepared(true).ToSQL()
   835  	fmt.Println(sql, args)
   836  
   837  	// Output:
   838  	// SELECT * FROM "test" WHERE ((a + b) BETWEEN 1 AND 10) []
   839  	// SELECT * FROM "test" WHERE ((a + b) BETWEEN ? AND ?) [1 10]
   840  	// SELECT * FROM "test" WHERE ((a + b) NOT BETWEEN 1 AND 10) []
   841  	// SELECT * FROM "test" WHERE ((a + b) NOT BETWEEN ? AND ?) [1 10]
   842  }
   843  
   844  func ExampleLAST() {
   845  	ds := goqu.From("test").Select(goqu.LAST("col"))
   846  	sql, args, _ := ds.ToSQL()
   847  	fmt.Println(sql, args)
   848  
   849  	sql, args, _ = ds.Prepared(true).ToSQL()
   850  	fmt.Println(sql, args)
   851  	// Output:
   852  	// SELECT LAST("col") FROM "test" []
   853  	// SELECT LAST("col") FROM "test" []
   854  }
   855  
   856  func ExampleLAST_as() {
   857  	sql, _, _ := goqu.From("test").Select(goqu.LAST("a").As("a")).ToSQL()
   858  	fmt.Println(sql)
   859  
   860  	// Output:
   861  	// SELECT LAST("a") AS "a" FROM "test"
   862  }
   863  
   864  func ExampleMAX() {
   865  	ds := goqu.From("test").Select(goqu.MAX("col"))
   866  	sql, args, _ := ds.ToSQL()
   867  	fmt.Println(sql, args)
   868  
   869  	sql, args, _ = ds.Prepared(true).ToSQL()
   870  	fmt.Println(sql, args)
   871  	// Output:
   872  	// SELECT MAX("col") FROM "test" []
   873  	// SELECT MAX("col") FROM "test" []
   874  }
   875  
   876  func ExampleMAX_as() {
   877  	sql, _, _ := goqu.From("test").Select(goqu.MAX("a").As("a")).ToSQL()
   878  	fmt.Println(sql)
   879  
   880  	// Output:
   881  	// SELECT MAX("a") AS "a" FROM "test"
   882  }
   883  
   884  func ExampleMAX_havingClause() {
   885  	ds := goqu.
   886  		From("test").
   887  		Select(goqu.MAX("a").As("MAX")).
   888  		GroupBy("a").
   889  		Having(goqu.MAX("a").Gt(10))
   890  
   891  	sql, args, _ := ds.ToSQL()
   892  	fmt.Println(sql, args)
   893  
   894  	sql, args, _ = ds.Prepared(true).ToSQL()
   895  	fmt.Println(sql, args)
   896  
   897  	// Output:
   898  	// SELECT MAX("a") AS "MAX" FROM "test" GROUP BY "a" HAVING (MAX("a") > 10) []
   899  	// SELECT MAX("a") AS "MAX" FROM "test" GROUP BY "a" HAVING (MAX("a") > ?) [10]
   900  }
   901  
   902  func ExampleMIN() {
   903  	ds := goqu.From("test").Select(goqu.MIN("col"))
   904  	sql, args, _ := ds.ToSQL()
   905  	fmt.Println(sql, args)
   906  
   907  	sql, args, _ = ds.Prepared(true).ToSQL()
   908  	fmt.Println(sql, args)
   909  	// Output:
   910  	// SELECT MIN("col") FROM "test" []
   911  	// SELECT MIN("col") FROM "test" []
   912  }
   913  
   914  func ExampleMIN_as() {
   915  	sql, _, _ := goqu.From("test").Select(goqu.MIN("a").As("a")).ToSQL()
   916  	fmt.Println(sql)
   917  
   918  	// Output:
   919  	// SELECT MIN("a") AS "a" FROM "test"
   920  }
   921  
   922  func ExampleMIN_havingClause() {
   923  	ds := goqu.
   924  		From("test").
   925  		Select(goqu.MIN("a").As("MIN")).
   926  		GroupBy("a").
   927  		Having(goqu.MIN("a").Gt(10))
   928  
   929  	sql, args, _ := ds.ToSQL()
   930  	fmt.Println(sql, args)
   931  
   932  	sql, args, _ = ds.Prepared(true).ToSQL()
   933  	fmt.Println(sql, args)
   934  
   935  	// Output:
   936  	// SELECT MIN("a") AS "MIN" FROM "test" GROUP BY "a" HAVING (MIN("a") > 10) []
   937  	// SELECT MIN("a") AS "MIN" FROM "test" GROUP BY "a" HAVING (MIN("a") > ?) [10]
   938  }
   939  
   940  func ExampleOn() {
   941  	ds := goqu.From("test").Join(
   942  		goqu.T("my_table"),
   943  		goqu.On(goqu.I("my_table.fkey").Eq(goqu.I("other_table.id"))),
   944  	)
   945  
   946  	sql, args, _ := ds.ToSQL()
   947  	fmt.Println(sql, args)
   948  
   949  	sql, args, _ = ds.Prepared(true).ToSQL()
   950  	fmt.Println(sql, args)
   951  	// Output:
   952  	// SELECT * FROM "test" INNER JOIN "my_table" ON ("my_table"."fkey" = "other_table"."id") []
   953  	// SELECT * FROM "test" INNER JOIN "my_table" ON ("my_table"."fkey" = "other_table"."id") []
   954  }
   955  
   956  func ExampleOn_withEx() {
   957  	ds := goqu.From("test").Join(
   958  		goqu.T("my_table"),
   959  		goqu.On(goqu.Ex{"my_table.fkey": goqu.I("other_table.id")}),
   960  	)
   961  
   962  	sql, args, _ := ds.ToSQL()
   963  	fmt.Println(sql, args)
   964  
   965  	sql, args, _ = ds.Prepared(true).ToSQL()
   966  	fmt.Println(sql, args)
   967  	// Output:
   968  	// SELECT * FROM "test" INNER JOIN "my_table" ON ("my_table"."fkey" = "other_table"."id") []
   969  	// SELECT * FROM "test" INNER JOIN "my_table" ON ("my_table"."fkey" = "other_table"."id") []
   970  }
   971  
   972  func ExampleOr() {
   973  	ds := goqu.From("test").Where(
   974  		goqu.Or(
   975  			goqu.C("col").Eq(10),
   976  			goqu.C("col").Eq(20),
   977  		),
   978  	)
   979  	sql, args, _ := ds.ToSQL()
   980  	fmt.Println(sql, args)
   981  
   982  	sql, args, _ = ds.Prepared(true).ToSQL()
   983  	fmt.Println(sql, args)
   984  
   985  	// Output:
   986  	// SELECT * FROM "test" WHERE (("col" = 10) OR ("col" = 20)) []
   987  	// SELECT * FROM "test" WHERE (("col" = ?) OR ("col" = ?)) [10 20]
   988  }
   989  
   990  func ExampleOr_withAnd() {
   991  	ds := goqu.From("items").Where(
   992  		goqu.Or(
   993  			goqu.C("a").Gt(10),
   994  			goqu.And(
   995  				goqu.C("b").Eq(100),
   996  				goqu.C("c").Neq("test"),
   997  			),
   998  		),
   999  	)
  1000  	sql, args, _ := ds.ToSQL()
  1001  	fmt.Println(sql, args)
  1002  
  1003  	sql, args, _ = ds.Prepared(true).ToSQL()
  1004  	fmt.Println(sql, args)
  1005  	// Output:
  1006  	// SELECT * FROM "items" WHERE (("a" > 10) OR (("b" = 100) AND ("c" != 'test'))) []
  1007  	// SELECT * FROM "items" WHERE (("a" > ?) OR (("b" = ?) AND ("c" != ?))) [10 100 test]
  1008  }
  1009  
  1010  func ExampleOr_withExMap() {
  1011  	ds := goqu.From("test").Where(
  1012  		goqu.Or(
  1013  			// Ex will be anded together
  1014  			goqu.Ex{
  1015  				"col1": 1,
  1016  				"col2": true,
  1017  			},
  1018  			goqu.Ex{
  1019  				"col3": nil,
  1020  				"col4": "foo",
  1021  			},
  1022  		),
  1023  	)
  1024  	sql, args, _ := ds.ToSQL()
  1025  	fmt.Println(sql, args)
  1026  
  1027  	sql, args, _ = ds.Prepared(true).ToSQL()
  1028  	fmt.Println(sql, args)
  1029  
  1030  	// Output:
  1031  	// SELECT * FROM "test" WHERE ((("col1" = 1) AND ("col2" IS TRUE)) OR (("col3" IS NULL) AND ("col4" = 'foo'))) []
  1032  	// SELECT * FROM "test" WHERE ((("col1" = ?) AND ("col2" IS TRUE)) OR (("col3" IS NULL) AND ("col4" = ?))) [1 foo]
  1033  }
  1034  
  1035  func ExampleRange_numbers() {
  1036  	ds := goqu.From("test").Where(
  1037  		goqu.C("col").Between(goqu.Range(1, 10)),
  1038  	)
  1039  	sql, args, _ := ds.ToSQL()
  1040  	fmt.Println(sql, args)
  1041  
  1042  	sql, args, _ = ds.Prepared(true).ToSQL()
  1043  	fmt.Println(sql, args)
  1044  
  1045  	ds = goqu.From("test").Where(
  1046  		goqu.C("col").NotBetween(goqu.Range(1, 10)),
  1047  	)
  1048  	sql, args, _ = ds.ToSQL()
  1049  	fmt.Println(sql, args)
  1050  
  1051  	sql, args, _ = ds.Prepared(true).ToSQL()
  1052  	fmt.Println(sql, args)
  1053  
  1054  	// Output:
  1055  	// SELECT * FROM "test" WHERE ("col" BETWEEN 1 AND 10) []
  1056  	// SELECT * FROM "test" WHERE ("col" BETWEEN ? AND ?) [1 10]
  1057  	// SELECT * FROM "test" WHERE ("col" NOT BETWEEN 1 AND 10) []
  1058  	// SELECT * FROM "test" WHERE ("col" NOT BETWEEN ? AND ?) [1 10]
  1059  }
  1060  
  1061  func ExampleRange_strings() {
  1062  	ds := goqu.From("test").Where(
  1063  		goqu.C("col").Between(goqu.Range("a", "z")),
  1064  	)
  1065  	sql, args, _ := ds.ToSQL()
  1066  	fmt.Println(sql, args)
  1067  
  1068  	sql, args, _ = ds.Prepared(true).ToSQL()
  1069  	fmt.Println(sql, args)
  1070  
  1071  	ds = goqu.From("test").Where(
  1072  		goqu.C("col").NotBetween(goqu.Range("a", "z")),
  1073  	)
  1074  	sql, args, _ = ds.ToSQL()
  1075  	fmt.Println(sql, args)
  1076  
  1077  	sql, args, _ = ds.Prepared(true).ToSQL()
  1078  	fmt.Println(sql, args)
  1079  
  1080  	// Output:
  1081  	// SELECT * FROM "test" WHERE ("col" BETWEEN 'a' AND 'z') []
  1082  	// SELECT * FROM "test" WHERE ("col" BETWEEN ? AND ?) [a z]
  1083  	// SELECT * FROM "test" WHERE ("col" NOT BETWEEN 'a' AND 'z') []
  1084  	// SELECT * FROM "test" WHERE ("col" NOT BETWEEN ? AND ?) [a z]
  1085  }
  1086  
  1087  func ExampleRange_identifiers() {
  1088  	ds := goqu.From("test").Where(
  1089  		goqu.C("col1").Between(goqu.Range(goqu.C("col2"), goqu.C("col3"))),
  1090  	)
  1091  	sql, args, _ := ds.ToSQL()
  1092  	fmt.Println(sql, args)
  1093  
  1094  	sql, args, _ = ds.Prepared(true).ToSQL()
  1095  	fmt.Println(sql, args)
  1096  
  1097  	ds = goqu.From("test").Where(
  1098  		goqu.C("col1").NotBetween(goqu.Range(goqu.C("col2"), goqu.C("col3"))),
  1099  	)
  1100  	sql, args, _ = ds.ToSQL()
  1101  	fmt.Println(sql, args)
  1102  
  1103  	sql, args, _ = ds.Prepared(true).ToSQL()
  1104  	fmt.Println(sql, args)
  1105  
  1106  	// Output:
  1107  	// SELECT * FROM "test" WHERE ("col1" BETWEEN "col2" AND "col3") []
  1108  	// SELECT * FROM "test" WHERE ("col1" BETWEEN "col2" AND "col3") []
  1109  	// SELECT * FROM "test" WHERE ("col1" NOT BETWEEN "col2" AND "col3") []
  1110  	// SELECT * FROM "test" WHERE ("col1" NOT BETWEEN "col2" AND "col3") []
  1111  }
  1112  
  1113  func ExampleS() {
  1114  	s := goqu.S("test_schema")
  1115  	t := s.Table("test")
  1116  	sql, args, _ := goqu.
  1117  		From(t).
  1118  		Select(
  1119  			t.Col("col1"),
  1120  			t.Col("col2"),
  1121  			t.Col("col3"),
  1122  		).
  1123  		ToSQL()
  1124  	fmt.Println(sql, args)
  1125  
  1126  	// Output:
  1127  	// SELECT "test_schema"."test"."col1", "test_schema"."test"."col2", "test_schema"."test"."col3" FROM "test_schema"."test" []
  1128  }
  1129  
  1130  func ExampleSUM() {
  1131  	ds := goqu.From("test").Select(goqu.SUM("col"))
  1132  	sql, args, _ := ds.ToSQL()
  1133  	fmt.Println(sql, args)
  1134  
  1135  	sql, args, _ = ds.Prepared(true).ToSQL()
  1136  	fmt.Println(sql, args)
  1137  	// Output:
  1138  	// SELECT SUM("col") FROM "test" []
  1139  	// SELECT SUM("col") FROM "test" []
  1140  }
  1141  
  1142  func ExampleSUM_as() {
  1143  	sql, _, _ := goqu.From("test").Select(goqu.SUM("a").As("a")).ToSQL()
  1144  	fmt.Println(sql)
  1145  
  1146  	// Output:
  1147  	// SELECT SUM("a") AS "a" FROM "test"
  1148  }
  1149  
  1150  func ExampleSUM_havingClause() {
  1151  	ds := goqu.
  1152  		From("test").
  1153  		Select(goqu.SUM("a").As("SUM")).
  1154  		GroupBy("a").
  1155  		Having(goqu.SUM("a").Gt(10))
  1156  
  1157  	sql, args, _ := ds.ToSQL()
  1158  	fmt.Println(sql, args)
  1159  
  1160  	sql, args, _ = ds.Prepared(true).ToSQL()
  1161  	fmt.Println(sql, args)
  1162  
  1163  	// Output:
  1164  	// SELECT SUM("a") AS "SUM" FROM "test" GROUP BY "a" HAVING (SUM("a") > 10) []
  1165  	// SELECT SUM("a") AS "SUM" FROM "test" GROUP BY "a" HAVING (SUM("a") > ?) [10]
  1166  }
  1167  
  1168  func ExampleStar() {
  1169  	ds := goqu.From("test").Select(goqu.Star())
  1170  
  1171  	sql, args, _ := ds.ToSQL()
  1172  	fmt.Println(sql, args)
  1173  
  1174  	sql, args, _ = ds.Prepared(true).ToSQL()
  1175  	fmt.Println(sql, args)
  1176  
  1177  	// Output:
  1178  	// SELECT * FROM "test" []
  1179  	// SELECT * FROM "test" []
  1180  }
  1181  
  1182  func ExampleT() {
  1183  	t := goqu.T("test")
  1184  	sql, args, _ := goqu.
  1185  		From(t).
  1186  		Select(
  1187  			t.Col("col1"),
  1188  			t.Col("col2"),
  1189  			t.Col("col3"),
  1190  		).
  1191  		ToSQL()
  1192  	fmt.Println(sql, args)
  1193  
  1194  	// Output:
  1195  	// SELECT "test"."col1", "test"."col2", "test"."col3" FROM "test" []
  1196  }
  1197  
  1198  func ExampleUsing() {
  1199  	ds := goqu.From("test").Join(
  1200  		goqu.T("my_table"),
  1201  		goqu.Using("fkey"),
  1202  	)
  1203  
  1204  	sql, args, _ := ds.ToSQL()
  1205  	fmt.Println(sql, args)
  1206  
  1207  	sql, args, _ = ds.Prepared(true).ToSQL()
  1208  	fmt.Println(sql, args)
  1209  	// Output:
  1210  	// SELECT * FROM "test" INNER JOIN "my_table" USING ("fkey") []
  1211  	// SELECT * FROM "test" INNER JOIN "my_table" USING ("fkey") []
  1212  }
  1213  
  1214  func ExampleUsing_withIdentifier() {
  1215  	ds := goqu.From("test").Join(
  1216  		goqu.T("my_table"),
  1217  		goqu.Using(goqu.C("fkey")),
  1218  	)
  1219  
  1220  	sql, args, _ := ds.ToSQL()
  1221  	fmt.Println(sql, args)
  1222  
  1223  	sql, args, _ = ds.Prepared(true).ToSQL()
  1224  	fmt.Println(sql, args)
  1225  	// Output:
  1226  	// SELECT * FROM "test" INNER JOIN "my_table" USING ("fkey") []
  1227  	// SELECT * FROM "test" INNER JOIN "my_table" USING ("fkey") []
  1228  }
  1229  
  1230  func ExampleEx() {
  1231  	ds := goqu.From("items").Where(
  1232  		goqu.Ex{
  1233  			"col1": "a",
  1234  			"col2": 1,
  1235  			"col3": true,
  1236  			"col4": false,
  1237  			"col5": nil,
  1238  			"col6": []string{"a", "b", "c"},
  1239  		},
  1240  	)
  1241  
  1242  	sql, args, _ := ds.ToSQL()
  1243  	fmt.Println(sql, args)
  1244  
  1245  	sql, args, _ = ds.Prepared(true).ToSQL()
  1246  	fmt.Println(sql, args)
  1247  
  1248  	// Output:
  1249  	// SELECT * FROM "items" WHERE (("col1" = 'a') AND ("col2" = 1) AND ("col3" IS TRUE) AND ("col4" IS FALSE) AND ("col5" IS NULL) AND ("col6" IN ('a', 'b', 'c'))) []
  1250  	// SELECT * FROM "items" WHERE (("col1" = ?) AND ("col2" = ?) AND ("col3" IS TRUE) AND ("col4" IS FALSE) AND ("col5" IS NULL) AND ("col6" IN (?, ?, ?))) [a 1 a b c]
  1251  }
  1252  
  1253  func ExampleEx_withOp() {
  1254  	sql, args, _ := goqu.From("items").Where(
  1255  		goqu.Ex{
  1256  			"col1": goqu.Op{"neq": "a"},
  1257  			"col3": goqu.Op{"isNot": true},
  1258  			"col6": goqu.Op{"notIn": []string{"a", "b", "c"}},
  1259  		},
  1260  	).ToSQL()
  1261  	fmt.Println(sql, args)
  1262  	// Output:
  1263  	// SELECT * FROM "items" WHERE (("col1" != 'a') AND ("col3" IS NOT TRUE) AND ("col6" NOT IN ('a', 'b', 'c'))) []
  1264  }
  1265  
  1266  func ExampleEx_in() {
  1267  	// using an Ex expression map
  1268  	sql, _, _ := goqu.From("test").Where(goqu.Ex{
  1269  		"a": []string{"a", "b", "c"},
  1270  	}).ToSQL()
  1271  	fmt.Println(sql)
  1272  
  1273  	// Output:
  1274  	// SELECT * FROM "test" WHERE ("a" IN ('a', 'b', 'c'))
  1275  }
  1276  
  1277  func ExampleExOr() {
  1278  	sql, args, _ := goqu.From("items").Where(
  1279  		goqu.ExOr{
  1280  			"col1": "a",
  1281  			"col2": 1,
  1282  			"col3": true,
  1283  			"col4": false,
  1284  			"col5": nil,
  1285  			"col6": []string{"a", "b", "c"},
  1286  		},
  1287  	).ToSQL()
  1288  	fmt.Println(sql, args)
  1289  
  1290  	// nolint:lll // sql statements are long
  1291  	// Output:
  1292  	// SELECT * FROM "items" WHERE (("col1" = 'a') OR ("col2" = 1) OR ("col3" IS TRUE) OR ("col4" IS FALSE) OR ("col5" IS NULL) OR ("col6" IN ('a', 'b', 'c'))) []
  1293  }
  1294  
  1295  func ExampleExOr_withOp() {
  1296  	sql, _, _ := goqu.From("items").Where(goqu.ExOr{
  1297  		"col1": goqu.Op{"neq": "a"},
  1298  		"col3": goqu.Op{"isNot": true},
  1299  		"col6": goqu.Op{"notIn": []string{"a", "b", "c"}},
  1300  	}).ToSQL()
  1301  	fmt.Println(sql)
  1302  
  1303  	sql, _, _ = goqu.From("items").Where(goqu.ExOr{
  1304  		"col1": goqu.Op{"gt": 1},
  1305  		"col2": goqu.Op{"gte": 1},
  1306  		"col3": goqu.Op{"lt": 1},
  1307  		"col4": goqu.Op{"lte": 1},
  1308  	}).ToSQL()
  1309  	fmt.Println(sql)
  1310  
  1311  	sql, _, _ = goqu.From("items").Where(goqu.ExOr{
  1312  		"col1": goqu.Op{"like": "a%"},
  1313  		"col2": goqu.Op{"notLike": "a%"},
  1314  		"col3": goqu.Op{"iLike": "a%"},
  1315  		"col4": goqu.Op{"notILike": "a%"},
  1316  	}).ToSQL()
  1317  	fmt.Println(sql)
  1318  
  1319  	sql, _, _ = goqu.From("items").Where(goqu.ExOr{
  1320  		"col1": goqu.Op{"like": regexp.MustCompile("^[ab]")},
  1321  		"col2": goqu.Op{"notLike": regexp.MustCompile("^[ab]")},
  1322  		"col3": goqu.Op{"iLike": regexp.MustCompile("^[ab]")},
  1323  		"col4": goqu.Op{"notILike": regexp.MustCompile("^[ab]")},
  1324  	}).ToSQL()
  1325  	fmt.Println(sql)
  1326  
  1327  	// Output:
  1328  	// SELECT * FROM "items" WHERE (("col1" != 'a') OR ("col3" IS NOT TRUE) OR ("col6" NOT IN ('a', 'b', 'c')))
  1329  	// SELECT * FROM "items" WHERE (("col1" > 1) OR ("col2" >= 1) OR ("col3" < 1) OR ("col4" <= 1))
  1330  	// SELECT * FROM "items" WHERE (("col1" LIKE 'a%') OR ("col2" NOT LIKE 'a%') OR ("col3" ILIKE 'a%') OR ("col4" NOT ILIKE 'a%'))
  1331  	// SELECT * FROM "items" WHERE (("col1" ~ '^[ab]') OR ("col2" !~ '^[ab]') OR ("col3" ~* '^[ab]') OR ("col4" !~* '^[ab]'))
  1332  }
  1333  
  1334  func ExampleOp_comparisons() {
  1335  	ds := goqu.From("test").Where(goqu.Ex{
  1336  		"a": 10,
  1337  		"b": goqu.Op{"neq": 10},
  1338  		"c": goqu.Op{"gte": 10},
  1339  		"d": goqu.Op{"lt": 10},
  1340  		"e": goqu.Op{"lte": 10},
  1341  	})
  1342  
  1343  	sql, args, _ := ds.ToSQL()
  1344  	fmt.Println(sql, args)
  1345  
  1346  	sql, args, _ = ds.Prepared(true).ToSQL()
  1347  	fmt.Println(sql, args)
  1348  
  1349  	// Output:
  1350  	// SELECT * FROM "test" WHERE (("a" = 10) AND ("b" != 10) AND ("c" >= 10) AND ("d" < 10) AND ("e" <= 10)) []
  1351  	// SELECT * FROM "test" WHERE (("a" = ?) AND ("b" != ?) AND ("c" >= ?) AND ("d" < ?) AND ("e" <= ?)) [10 10 10 10 10]
  1352  }
  1353  
  1354  func ExampleOp_inComparisons() {
  1355  	// using an Ex expression map
  1356  	ds := goqu.From("test").Where(goqu.Ex{
  1357  		"a": goqu.Op{"in": []string{"a", "b", "c"}},
  1358  	})
  1359  
  1360  	sql, args, _ := ds.ToSQL()
  1361  	fmt.Println(sql, args)
  1362  
  1363  	sql, args, _ = ds.Prepared(true).ToSQL()
  1364  	fmt.Println(sql, args)
  1365  
  1366  	ds = goqu.From("test").Where(goqu.Ex{
  1367  		"a": goqu.Op{"notIn": []string{"a", "b", "c"}},
  1368  	})
  1369  
  1370  	sql, args, _ = ds.ToSQL()
  1371  	fmt.Println(sql, args)
  1372  
  1373  	sql, args, _ = ds.Prepared(true).ToSQL()
  1374  	fmt.Println(sql, args)
  1375  
  1376  	// Output:
  1377  	// SELECT * FROM "test" WHERE ("a" IN ('a', 'b', 'c')) []
  1378  	// SELECT * FROM "test" WHERE ("a" IN (?, ?, ?)) [a b c]
  1379  	// SELECT * FROM "test" WHERE ("a" NOT IN ('a', 'b', 'c')) []
  1380  	// SELECT * FROM "test" WHERE ("a" NOT IN (?, ?, ?)) [a b c]
  1381  }
  1382  
  1383  func ExampleOp_likeComparisons() {
  1384  	// using an Ex expression map
  1385  	ds := goqu.From("test").Where(goqu.Ex{
  1386  		"a": goqu.Op{"like": "%a%"},
  1387  	})
  1388  	sql, args, _ := ds.ToSQL()
  1389  	fmt.Println(sql, args)
  1390  
  1391  	sql, args, _ = ds.Prepared(true).ToSQL()
  1392  	fmt.Println(sql, args)
  1393  
  1394  	ds = goqu.From("test").Where(goqu.Ex{
  1395  		"a": goqu.Op{"like": regexp.MustCompile("[ab]")},
  1396  	})
  1397  
  1398  	sql, args, _ = ds.ToSQL()
  1399  	fmt.Println(sql, args)
  1400  
  1401  	sql, args, _ = ds.Prepared(true).ToSQL()
  1402  	fmt.Println(sql, args)
  1403  
  1404  	ds = goqu.From("test").Where(goqu.Ex{
  1405  		"a": goqu.Op{"iLike": "%a%"},
  1406  	})
  1407  
  1408  	sql, args, _ = ds.ToSQL()
  1409  	fmt.Println(sql, args)
  1410  
  1411  	sql, args, _ = ds.Prepared(true).ToSQL()
  1412  	fmt.Println(sql, args)
  1413  
  1414  	ds = goqu.From("test").Where(goqu.Ex{
  1415  		"a": goqu.Op{"iLike": regexp.MustCompile("[ab]")},
  1416  	})
  1417  
  1418  	sql, args, _ = ds.ToSQL()
  1419  	fmt.Println(sql, args)
  1420  
  1421  	sql, args, _ = ds.Prepared(true).ToSQL()
  1422  	fmt.Println(sql, args)
  1423  
  1424  	ds = goqu.From("test").Where(goqu.Ex{
  1425  		"a": goqu.Op{"notLike": "%a%"},
  1426  	})
  1427  
  1428  	sql, args, _ = ds.ToSQL()
  1429  	fmt.Println(sql, args)
  1430  
  1431  	sql, args, _ = ds.Prepared(true).ToSQL()
  1432  	fmt.Println(sql, args)
  1433  
  1434  	ds = goqu.From("test").Where(goqu.Ex{
  1435  		"a": goqu.Op{"notLike": regexp.MustCompile("[ab]")},
  1436  	})
  1437  
  1438  	sql, args, _ = ds.ToSQL()
  1439  	fmt.Println(sql, args)
  1440  
  1441  	sql, args, _ = ds.Prepared(true).ToSQL()
  1442  	fmt.Println(sql, args)
  1443  
  1444  	ds = goqu.From("test").Where(goqu.Ex{
  1445  		"a": goqu.Op{"notILike": "%a%"},
  1446  	})
  1447  
  1448  	sql, args, _ = ds.ToSQL()
  1449  	fmt.Println(sql, args)
  1450  
  1451  	sql, args, _ = ds.Prepared(true).ToSQL()
  1452  	fmt.Println(sql, args)
  1453  
  1454  	ds = goqu.From("test").Where(goqu.Ex{
  1455  		"a": goqu.Op{"notILike": regexp.MustCompile("[ab]")},
  1456  	})
  1457  	sql, args, _ = ds.ToSQL()
  1458  	fmt.Println(sql, args)
  1459  
  1460  	sql, args, _ = ds.Prepared(true).ToSQL()
  1461  	fmt.Println(sql, args)
  1462  
  1463  	// Output:
  1464  	// SELECT * FROM "test" WHERE ("a" LIKE '%a%') []
  1465  	// SELECT * FROM "test" WHERE ("a" LIKE ?) [%a%]
  1466  	// SELECT * FROM "test" WHERE ("a" ~ '[ab]') []
  1467  	// SELECT * FROM "test" WHERE ("a" ~ ?) [[ab]]
  1468  	// SELECT * FROM "test" WHERE ("a" ILIKE '%a%') []
  1469  	// SELECT * FROM "test" WHERE ("a" ILIKE ?) [%a%]
  1470  	// SELECT * FROM "test" WHERE ("a" ~* '[ab]') []
  1471  	// SELECT * FROM "test" WHERE ("a" ~* ?) [[ab]]
  1472  	// SELECT * FROM "test" WHERE ("a" NOT LIKE '%a%') []
  1473  	// SELECT * FROM "test" WHERE ("a" NOT LIKE ?) [%a%]
  1474  	// SELECT * FROM "test" WHERE ("a" !~ '[ab]') []
  1475  	// SELECT * FROM "test" WHERE ("a" !~ ?) [[ab]]
  1476  	// SELECT * FROM "test" WHERE ("a" NOT ILIKE '%a%') []
  1477  	// SELECT * FROM "test" WHERE ("a" NOT ILIKE ?) [%a%]
  1478  	// SELECT * FROM "test" WHERE ("a" !~* '[ab]') []
  1479  	// SELECT * FROM "test" WHERE ("a" !~* ?) [[ab]]
  1480  }
  1481  
  1482  func ExampleOp_isComparisons() {
  1483  	// using an Ex expression map
  1484  	ds := goqu.From("test").Where(goqu.Ex{
  1485  		"a": true,
  1486  	})
  1487  	sql, args, _ := ds.ToSQL()
  1488  	fmt.Println(sql, args)
  1489  	sql, args, _ = ds.Prepared(true).ToSQL()
  1490  	fmt.Println(sql, args)
  1491  
  1492  	ds = goqu.From("test").Where(goqu.Ex{
  1493  		"a": goqu.Op{"is": true},
  1494  	})
  1495  	sql, args, _ = ds.ToSQL()
  1496  	fmt.Println(sql, args)
  1497  	sql, args, _ = ds.Prepared(true).ToSQL()
  1498  	fmt.Println(sql, args)
  1499  
  1500  	ds = goqu.From("test").Where(goqu.Ex{
  1501  		"a": false,
  1502  	})
  1503  	sql, args, _ = ds.ToSQL()
  1504  	fmt.Println(sql, args)
  1505  	sql, args, _ = ds.Prepared(true).ToSQL()
  1506  	fmt.Println(sql, args)
  1507  
  1508  	ds = goqu.From("test").Where(goqu.Ex{
  1509  		"a": goqu.Op{"is": false},
  1510  	})
  1511  	sql, args, _ = ds.ToSQL()
  1512  	fmt.Println(sql, args)
  1513  	sql, args, _ = ds.Prepared(true).ToSQL()
  1514  	fmt.Println(sql, args)
  1515  
  1516  	ds = goqu.From("test").Where(goqu.Ex{
  1517  		"a": nil,
  1518  	})
  1519  	sql, args, _ = ds.ToSQL()
  1520  	fmt.Println(sql, args)
  1521  	sql, args, _ = ds.Prepared(true).ToSQL()
  1522  	fmt.Println(sql, args)
  1523  
  1524  	ds = goqu.From("test").Where(goqu.Ex{
  1525  		"a": goqu.Op{"is": nil},
  1526  	})
  1527  	sql, args, _ = ds.ToSQL()
  1528  	fmt.Println(sql, args)
  1529  	sql, args, _ = ds.Prepared(true).ToSQL()
  1530  	fmt.Println(sql, args)
  1531  
  1532  	ds = goqu.From("test").Where(goqu.Ex{
  1533  		"a": goqu.Op{"isNot": true},
  1534  	})
  1535  	sql, args, _ = ds.ToSQL()
  1536  	fmt.Println(sql, args)
  1537  	sql, args, _ = ds.Prepared(true).ToSQL()
  1538  	fmt.Println(sql, args)
  1539  
  1540  	ds = goqu.From("test").Where(goqu.Ex{
  1541  		"a": goqu.Op{"isNot": false},
  1542  	})
  1543  	sql, args, _ = ds.ToSQL()
  1544  	fmt.Println(sql, args)
  1545  	sql, args, _ = ds.Prepared(true).ToSQL()
  1546  	fmt.Println(sql, args)
  1547  
  1548  	ds = goqu.From("test").Where(goqu.Ex{
  1549  		"a": goqu.Op{"isNot": nil},
  1550  	})
  1551  	sql, args, _ = ds.ToSQL()
  1552  	fmt.Println(sql, args)
  1553  	sql, args, _ = ds.Prepared(true).ToSQL()
  1554  	fmt.Println(sql, args)
  1555  
  1556  	// Output:
  1557  	// SELECT * FROM "test" WHERE ("a" IS TRUE) []
  1558  	// SELECT * FROM "test" WHERE ("a" IS TRUE) []
  1559  	// SELECT * FROM "test" WHERE ("a" IS TRUE) []
  1560  	// SELECT * FROM "test" WHERE ("a" IS TRUE) []
  1561  	// SELECT * FROM "test" WHERE ("a" IS FALSE) []
  1562  	// SELECT * FROM "test" WHERE ("a" IS FALSE) []
  1563  	// SELECT * FROM "test" WHERE ("a" IS FALSE) []
  1564  	// SELECT * FROM "test" WHERE ("a" IS FALSE) []
  1565  	// SELECT * FROM "test" WHERE ("a" IS NULL) []
  1566  	// SELECT * FROM "test" WHERE ("a" IS NULL) []
  1567  	// SELECT * FROM "test" WHERE ("a" IS NULL) []
  1568  	// SELECT * FROM "test" WHERE ("a" IS NULL) []
  1569  	// SELECT * FROM "test" WHERE ("a" IS NOT TRUE) []
  1570  	// SELECT * FROM "test" WHERE ("a" IS NOT TRUE) []
  1571  	// SELECT * FROM "test" WHERE ("a" IS NOT FALSE) []
  1572  	// SELECT * FROM "test" WHERE ("a" IS NOT FALSE) []
  1573  	// SELECT * FROM "test" WHERE ("a" IS NOT NULL) []
  1574  	// SELECT * FROM "test" WHERE ("a" IS NOT NULL) []
  1575  }
  1576  
  1577  func ExampleOp_betweenComparisons() {
  1578  	ds := goqu.From("test").Where(goqu.Ex{
  1579  		"a": goqu.Op{"between": goqu.Range(1, 10)},
  1580  	})
  1581  	sql, args, _ := ds.ToSQL()
  1582  	fmt.Println(sql, args)
  1583  
  1584  	sql, args, _ = ds.Prepared(true).ToSQL()
  1585  	fmt.Println(sql, args)
  1586  
  1587  	ds = goqu.From("test").Where(goqu.Ex{
  1588  		"a": goqu.Op{"notBetween": goqu.Range(1, 10)},
  1589  	})
  1590  	sql, args, _ = ds.ToSQL()
  1591  	fmt.Println(sql, args)
  1592  
  1593  	sql, args, _ = ds.Prepared(true).ToSQL()
  1594  	fmt.Println(sql, args)
  1595  
  1596  	// Output:
  1597  	// SELECT * FROM "test" WHERE ("a" BETWEEN 1 AND 10) []
  1598  	// SELECT * FROM "test" WHERE ("a" BETWEEN ? AND ?) [1 10]
  1599  	// SELECT * FROM "test" WHERE ("a" NOT BETWEEN 1 AND 10) []
  1600  	// SELECT * FROM "test" WHERE ("a" NOT BETWEEN ? AND ?) [1 10]
  1601  }
  1602  
  1603  // When using a single op with multiple keys they are ORed together
  1604  func ExampleOp_withMultipleKeys() {
  1605  	ds := goqu.From("items").Where(goqu.Ex{
  1606  		"col1": goqu.Op{"is": nil, "eq": 10},
  1607  	})
  1608  
  1609  	sql, args, _ := ds.ToSQL()
  1610  	fmt.Println(sql, args)
  1611  
  1612  	sql, args, _ = ds.Prepared(true).ToSQL()
  1613  	fmt.Println(sql, args)
  1614  
  1615  	// Output:
  1616  	// SELECT * FROM "items" WHERE (("col1" = 10) OR ("col1" IS NULL)) []
  1617  	// SELECT * FROM "items" WHERE (("col1" = ?) OR ("col1" IS NULL)) [10]
  1618  }
  1619  
  1620  func ExampleRecord_insert() {
  1621  	ds := goqu.Insert("test")
  1622  
  1623  	records := []goqu.Record{
  1624  		{"col1": 1, "col2": "foo"},
  1625  		{"col1": 2, "col2": "bar"},
  1626  	}
  1627  
  1628  	sql, args, _ := ds.Rows(records).ToSQL()
  1629  	fmt.Println(sql, args)
  1630  
  1631  	sql, args, _ = ds.Prepared(true).Rows(records).ToSQL()
  1632  	fmt.Println(sql, args)
  1633  	// Output:
  1634  	// INSERT INTO "test" ("col1", "col2") VALUES (1, 'foo'), (2, 'bar') []
  1635  	// INSERT INTO "test" ("col1", "col2") VALUES (?, ?), (?, ?) [1 foo 2 bar]
  1636  }
  1637  
  1638  func ExampleRecord_update() {
  1639  	ds := goqu.Update("test")
  1640  	update := goqu.Record{"col1": 1, "col2": "foo"}
  1641  
  1642  	sql, args, _ := ds.Set(update).ToSQL()
  1643  	fmt.Println(sql, args)
  1644  
  1645  	sql, args, _ = ds.Prepared(true).Set(update).ToSQL()
  1646  	fmt.Println(sql, args)
  1647  	// Output:
  1648  	// UPDATE "test" SET "col1"=1,"col2"='foo' []
  1649  	// UPDATE "test" SET "col1"=?,"col2"=? [1 foo]
  1650  }
  1651  
  1652  func ExampleV() {
  1653  	ds := goqu.From("user").Select(
  1654  		goqu.V(true).As("is_verified"),
  1655  		goqu.V(1.2).As("version"),
  1656  		"first_name",
  1657  		"last_name",
  1658  	)
  1659  
  1660  	sql, args, _ := ds.ToSQL()
  1661  	fmt.Println(sql, args)
  1662  
  1663  	ds = goqu.From("user").Where(goqu.V(1).Neq(1))
  1664  	sql, args, _ = ds.ToSQL()
  1665  	fmt.Println(sql, args)
  1666  
  1667  	// Output:
  1668  	// SELECT TRUE AS "is_verified", 1.2 AS "version", "first_name", "last_name" FROM "user" []
  1669  	// SELECT * FROM "user" WHERE (1 != 1) []
  1670  }
  1671  
  1672  func ExampleV_prepared() {
  1673  	ds := goqu.From("user").Select(
  1674  		goqu.V(true).As("is_verified"),
  1675  		goqu.V(1.2).As("version"),
  1676  		"first_name",
  1677  		"last_name",
  1678  	)
  1679  
  1680  	sql, args, _ := ds.Prepared(true).ToSQL()
  1681  	fmt.Println(sql, args)
  1682  
  1683  	ds = goqu.From("user").Where(goqu.V(1).Neq(1))
  1684  
  1685  	sql, args, _ = ds.Prepared(true).ToSQL()
  1686  	fmt.Println(sql, args)
  1687  
  1688  	// Output:
  1689  	// SELECT ? AS "is_verified", ? AS "version", "first_name", "last_name" FROM "user" [true 1.2]
  1690  	// SELECT * FROM "user" WHERE (? != ?) [1 1]
  1691  }
  1692  
  1693  func ExampleVals() {
  1694  	ds := goqu.Insert("user").
  1695  		Cols("first_name", "last_name", "is_verified").
  1696  		Vals(
  1697  			goqu.Vals{"Greg", "Farley", true},
  1698  			goqu.Vals{"Jimmy", "Stewart", true},
  1699  			goqu.Vals{"Jeff", "Jeffers", false},
  1700  		)
  1701  	insertSQL, args, _ := ds.ToSQL()
  1702  	fmt.Println(insertSQL, args)
  1703  
  1704  	// Output:
  1705  	// INSERT INTO "user" ("first_name", "last_name", "is_verified") VALUES ('Greg', 'Farley', TRUE), ('Jimmy', 'Stewart', TRUE), ('Jeff', 'Jeffers', FALSE) []
  1706  }
  1707  
  1708  func ExampleW() {
  1709  	ds := goqu.From("test").
  1710  		Select(goqu.ROW_NUMBER().Over(goqu.W().PartitionBy("a").OrderBy(goqu.I("b").Asc())))
  1711  	query, args, _ := ds.ToSQL()
  1712  	fmt.Println(query, args)
  1713  
  1714  	ds = goqu.From("test").
  1715  		Select(goqu.ROW_NUMBER().OverName(goqu.I("w"))).
  1716  		Window(goqu.W("w").PartitionBy("a").OrderBy(goqu.I("b").Asc()))
  1717  	query, args, _ = ds.ToSQL()
  1718  	fmt.Println(query, args)
  1719  
  1720  	ds = goqu.From("test").
  1721  		Select(goqu.ROW_NUMBER().OverName(goqu.I("w1"))).
  1722  		Window(
  1723  			goqu.W("w1").PartitionBy("a"),
  1724  			goqu.W("w").Inherit("w1").OrderBy(goqu.I("b").Asc()),
  1725  		)
  1726  	query, args, _ = ds.ToSQL()
  1727  	fmt.Println(query, args)
  1728  
  1729  	ds = goqu.From("test").
  1730  		Select(goqu.ROW_NUMBER().Over(goqu.W().Inherit("w").OrderBy("b"))).
  1731  		Window(goqu.W("w").PartitionBy("a"))
  1732  	query, args, _ = ds.ToSQL()
  1733  	fmt.Println(query, args)
  1734  	// Output:
  1735  	// SELECT ROW_NUMBER() OVER (PARTITION BY "a" ORDER BY "b" ASC) FROM "test" []
  1736  	// SELECT ROW_NUMBER() OVER "w" FROM "test" WINDOW "w" AS (PARTITION BY "a" ORDER BY "b" ASC) []
  1737  	// SELECT ROW_NUMBER() OVER "w1" FROM "test" WINDOW "w1" AS (PARTITION BY "a"), "w" AS ("w1" ORDER BY "b" ASC) []
  1738  	// SELECT ROW_NUMBER() OVER ("w" ORDER BY "b") FROM "test" WINDOW "w" AS (PARTITION BY "a") []
  1739  }
  1740  
  1741  func ExampleLateral() {
  1742  	maxEntry := goqu.From("entry").
  1743  		Select(goqu.MAX("int").As("max_int")).
  1744  		Where(goqu.Ex{"time": goqu.Op{"lt": goqu.I("e.time")}}).
  1745  		As("max_entry")
  1746  
  1747  	maxID := goqu.From("entry").
  1748  		Select("id").
  1749  		Where(goqu.Ex{"int": goqu.I("max_entry.max_int")}).
  1750  		As("max_id")
  1751  
  1752  	ds := goqu.
  1753  		Select("e.id", "max_entry.max_int", "max_id.id").
  1754  		From(
  1755  			goqu.T("entry").As("e"),
  1756  			goqu.Lateral(maxEntry),
  1757  			goqu.Lateral(maxID),
  1758  		)
  1759  	query, args, _ := ds.ToSQL()
  1760  	fmt.Println(query, args)
  1761  
  1762  	query, args, _ = ds.Prepared(true).ToSQL()
  1763  	fmt.Println(query, args)
  1764  
  1765  	// Output:
  1766  	// SELECT "e"."id", "max_entry"."max_int", "max_id"."id" FROM "entry" AS "e", LATERAL (SELECT MAX("int") AS "max_int" FROM "entry" WHERE ("time" < "e"."time")) AS "max_entry", LATERAL (SELECT "id" FROM "entry" WHERE ("int" = "max_entry"."max_int")) AS "max_id" []
  1767  	// SELECT "e"."id", "max_entry"."max_int", "max_id"."id" FROM "entry" AS "e", LATERAL (SELECT MAX("int") AS "max_int" FROM "entry" WHERE ("time" < "e"."time")) AS "max_entry", LATERAL (SELECT "id" FROM "entry" WHERE ("int" = "max_entry"."max_int")) AS "max_id" []
  1768  }
  1769  
  1770  func ExampleLateral_join() {
  1771  	maxEntry := goqu.From("entry").
  1772  		Select(goqu.MAX("int").As("max_int")).
  1773  		Where(goqu.Ex{"time": goqu.Op{"lt": goqu.I("e.time")}}).
  1774  		As("max_entry")
  1775  
  1776  	maxID := goqu.From("entry").
  1777  		Select("id").
  1778  		Where(goqu.Ex{"int": goqu.I("max_entry.max_int")}).
  1779  		As("max_id")
  1780  
  1781  	ds := goqu.
  1782  		Select("e.id", "max_entry.max_int", "max_id.id").
  1783  		From(goqu.T("entry").As("e")).
  1784  		Join(goqu.Lateral(maxEntry), goqu.On(goqu.V(true))).
  1785  		Join(goqu.Lateral(maxID), goqu.On(goqu.V(true)))
  1786  	query, args, _ := ds.ToSQL()
  1787  	fmt.Println(query, args)
  1788  
  1789  	query, args, _ = ds.Prepared(true).ToSQL()
  1790  	fmt.Println(query, args)
  1791  
  1792  	// Output:
  1793  	// SELECT "e"."id", "max_entry"."max_int", "max_id"."id" FROM "entry" AS "e" INNER JOIN LATERAL (SELECT MAX("int") AS "max_int" FROM "entry" WHERE ("time" < "e"."time")) AS "max_entry" ON TRUE INNER JOIN LATERAL (SELECT "id" FROM "entry" WHERE ("int" = "max_entry"."max_int")) AS "max_id" ON TRUE []
  1794  	// SELECT "e"."id", "max_entry"."max_int", "max_id"."id" FROM "entry" AS "e" INNER JOIN LATERAL (SELECT MAX("int") AS "max_int" FROM "entry" WHERE ("time" < "e"."time")) AS "max_entry" ON ? INNER JOIN LATERAL (SELECT "id" FROM "entry" WHERE ("int" = "max_entry"."max_int")) AS "max_id" ON ? [true true]
  1795  }
  1796  
  1797  func ExampleAny() {
  1798  	ds := goqu.From("test").Where(goqu.Ex{
  1799  		"id": goqu.Any(goqu.From("other").Select("test_id")),
  1800  	})
  1801  	sql, args, _ := ds.ToSQL()
  1802  	fmt.Println(sql, args)
  1803  
  1804  	sql, args, _ = ds.Prepared(true).ToSQL()
  1805  	fmt.Println(sql, args)
  1806  	// Output:
  1807  	// SELECT * FROM "test" WHERE ("id" = ANY ((SELECT "test_id" FROM "other"))) []
  1808  	// SELECT * FROM "test" WHERE ("id" = ANY ((SELECT "test_id" FROM "other"))) []
  1809  }
  1810  
  1811  func ExampleAll() {
  1812  	ds := goqu.From("test").Where(goqu.Ex{
  1813  		"id": goqu.All(goqu.From("other").Select("test_id")),
  1814  	})
  1815  	sql, args, _ := ds.ToSQL()
  1816  	fmt.Println(sql, args)
  1817  
  1818  	sql, args, _ = ds.Prepared(true).ToSQL()
  1819  	fmt.Println(sql, args)
  1820  	// Output:
  1821  	// SELECT * FROM "test" WHERE ("id" = ALL ((SELECT "test_id" FROM "other"))) []
  1822  	// SELECT * FROM "test" WHERE ("id" = ALL ((SELECT "test_id" FROM "other"))) []
  1823  }
  1824  
  1825  func ExampleCase_search() {
  1826  	ds := goqu.From("test").
  1827  		Select(
  1828  			goqu.C("col"),
  1829  			goqu.Case().
  1830  				When(goqu.C("col").Gt(0), true).
  1831  				When(goqu.C("col").Lte(0), false).
  1832  				As("is_gt_zero"),
  1833  		)
  1834  	sql, args, _ := ds.ToSQL()
  1835  	fmt.Println(sql, args)
  1836  
  1837  	sql, args, _ = ds.Prepared(true).ToSQL()
  1838  	fmt.Println(sql, args)
  1839  	// Output:
  1840  	// SELECT "col", CASE  WHEN ("col" > 0) THEN TRUE WHEN ("col" <= 0) THEN FALSE END AS "is_gt_zero" FROM "test" []
  1841  	// SELECT "col", CASE  WHEN ("col" > ?) THEN ? WHEN ("col" <= ?) THEN ? END AS "is_gt_zero" FROM "test" [0 true 0 false]
  1842  }
  1843  
  1844  func ExampleCase_searchElse() {
  1845  	ds := goqu.From("test").
  1846  		Select(
  1847  			goqu.C("col"),
  1848  			goqu.Case().
  1849  				When(goqu.C("col").Gt(10), "Gt 10").
  1850  				When(goqu.C("col").Gt(20), "Gt 20").
  1851  				Else("Bad Val").
  1852  				As("str_val"),
  1853  		)
  1854  	sql, args, _ := ds.ToSQL()
  1855  	fmt.Println(sql, args)
  1856  
  1857  	sql, args, _ = ds.Prepared(true).ToSQL()
  1858  	fmt.Println(sql, args)
  1859  	// Output:
  1860  	// SELECT "col", CASE  WHEN ("col" > 10) THEN 'Gt 10' WHEN ("col" > 20) THEN 'Gt 20' ELSE 'Bad Val' END AS "str_val" FROM "test" []
  1861  	// SELECT "col", CASE  WHEN ("col" > ?) THEN ? WHEN ("col" > ?) THEN ? ELSE ? END AS "str_val" FROM "test" [10 Gt 10 20 Gt 20 Bad Val]
  1862  }
  1863  
  1864  func ExampleCase_value() {
  1865  	ds := goqu.From("test").
  1866  		Select(
  1867  			goqu.C("col"),
  1868  			goqu.Case().
  1869  				Value(goqu.C("str")).
  1870  				When("foo", "FOO").
  1871  				When("bar", "BAR").
  1872  				As("foo_bar_upper"),
  1873  		)
  1874  	sql, args, _ := ds.ToSQL()
  1875  	fmt.Println(sql, args)
  1876  
  1877  	sql, args, _ = ds.Prepared(true).ToSQL()
  1878  	fmt.Println(sql, args)
  1879  	// Output:
  1880  	// SELECT "col", CASE "str" WHEN 'foo' THEN 'FOO' WHEN 'bar' THEN 'BAR' END AS "foo_bar_upper" FROM "test" []
  1881  	// SELECT "col", CASE "str" WHEN ? THEN ? WHEN ? THEN ? END AS "foo_bar_upper" FROM "test" [foo FOO bar BAR]
  1882  }
  1883  
  1884  func ExampleCase_valueElse() {
  1885  	ds := goqu.From("test").
  1886  		Select(
  1887  			goqu.C("col"),
  1888  			goqu.Case().
  1889  				Value(goqu.C("str")).
  1890  				When("foo", "FOO").
  1891  				When("bar", "BAR").
  1892  				Else("Baz").
  1893  				As("foo_bar_upper"),
  1894  		)
  1895  	sql, args, _ := ds.ToSQL()
  1896  	fmt.Println(sql, args)
  1897  
  1898  	sql, args, _ = ds.Prepared(true).ToSQL()
  1899  	fmt.Println(sql, args)
  1900  	// Output:
  1901  	// SELECT "col", CASE "str" WHEN 'foo' THEN 'FOO' WHEN 'bar' THEN 'BAR' ELSE 'Baz' END AS "foo_bar_upper" FROM "test" []
  1902  	// SELECT "col", CASE "str" WHEN ? THEN ? WHEN ? THEN ? ELSE ? END AS "foo_bar_upper" FROM "test" [foo FOO bar BAR Baz]
  1903  }
  1904  

View as plain text