pg

db := txExample()

incrInTx := func(db *pg.DB) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	// Rollback tx on error.
	defer tx.Rollback()

	var counter int
	_, err = tx.QueryOne(
		pg.Scan(&counter), `SELECT counter FROM tx_test FOR UPDATE`)
	if err != nil {
		return err
	}

	counter++

	_, err = tx.Exec(`UPDATE tx_test SET counter = ?`, counter)
	if err != nil {
		return err
	}

	return tx.Commit()
}

var wg sync.WaitGroup
for i := 0; i < 10; i++ {
	wg.Add(1)
	go func() {
		defer wg.Done()
		err := incrInTx(db)
		panicIf(err)
	}()
}
wg.Wait()

var counter int
_, err := db.QueryOne(pg.Scan(&counter), `SELECT counter FROM tx_test`)
panicIf(err)
fmt.Println(counter)

Output:

10

_, err := pgdb.Exec(`CREATE TEMP TABLE words(word text, len int)`)
panicIf(err)

r := strings.NewReader("hello,5\nfoo,3\n")
_, err = pgdb.CopyFrom(r, `COPY words FROM STDIN WITH CSV`)
panicIf(err)

var buf bytes.Buffer
_, err = pgdb.CopyTo(&buf, `COPY words TO STDOUT WITH CSV`)
panicIf(err)
fmt.Println(buf.String())

Output:

hello,5
foo,3

package main

import (
	"fmt"

	"github.com/go-pg/pg"
	"github.com/go-pg/pg/orm"
)

var pgdb *pg.DB

func panicIf(err error) {
	if err != nil {
		panic(err)
	}
}

func main() {
	type Model1 struct {
		Id int
	}

	type Model2 struct {
		Id   int
		Name string

		Model1Id int `sql:"on_delete:RESTRICT, on_update: CASCADE"`
		Model1   *Model1
	}

	for _, model := range []interface{}{&Model1{}, &Model2{}} {
		err := pgdb.CreateTable(model, &orm.CreateTableOptions{
			Temp:          true, // create temp table
			FKConstraints: true,
		})
		panicIf(err)
	}

	var info []struct {
		ColumnName string
		DataType   string
	}
	_, err := pgdb.Query(&info, `
		SELECT column_name, data_type
		FROM information_schema.columns
		WHERE table_name = 'model2'
	`)
	panicIf(err)
	fmt.Println(info)
}

Output:

[{id bigint} {name text} {model1_id bigint}]

db := modelDB()

book := Book{
	Title:    "title 1",
	AuthorID: 1,
}
err := db.Insert(&book)
if err != nil {
	panic(err)
}

err = db.Delete(&book)
if err != nil {
	panic(err)
}

err = db.Select(&book)
fmt.Println(err)

Output:

pg: no rows in result set

db := modelDB()

var books []Book
err := db.Model(&books).Select()
if err != nil {
	panic(err)
}

res, err := db.Model(&books).Delete()
if err != nil {
	panic(err)
}
fmt.Println("deleted", res.RowsAffected())

count, err := db.Model((*Book)(nil)).Count()
if err != nil {
	panic(err)
}
fmt.Println("left", count)

Output:

deleted 3
left 0

db := modelDB()

ids := pg.In([]int{1, 2, 3})
res, err := db.Model((*Book)(nil)).Where("id IN (?)", ids).Delete()
if err != nil {
	panic(err)
}
fmt.Println("deleted", res.RowsAffected())

count, err := db.Model((*Book)(nil)).Count()
if err != nil {
	panic(err)
}
fmt.Println("left", count)

Output:

deleted 3
left 0

package main

import (
	"fmt"

	"github.com/go-pg/pg"
)

var pgdb *pg.DB

func panicIf(err error) {
	if err != nil {
		panic(err)
	}
}

func main() {
	res, err := pgdb.Exec(`CREATE TEMP TABLE test()`)
	panicIf(err)
	fmt.Println(res.RowsAffected())
}

Output:

-1

db := modelDB()

book := Book{
	Title:    "new book",
	AuthorID: 1,
}

err := db.Insert(&book)
if err != nil {
	panic(err)
}
fmt.Println(book)

Output:

Book<Id=4 Title="new book">

db := modelDB()

book1 := Book{
	Title: "new book 1",
}
book2 := Book{
	Title: "new book 2",
}
err := db.Insert(&book1, &book2)
if err != nil {
	panic(err)
}
fmt.Println(book1, book2)

Output:

Book<Id=4 Title="new book 1"> Book<Id=5 Title="new book 2">

db := modelDB()

books := []Book{{
	Title: "new book 1",
}, {
	Title: "new book 2",
}}
err := db.Insert(&books)
if err != nil {
	panic(err)
}
fmt.Println(books)

Output:

[Book<Id=4 Title="new book 1"> Book<Id=5 Title="new book 2">]

type NamelessModel struct {
	tableName struct{} `sql:"_"` // "_" means no name
	Id        int
}

db := modelDB()

err := db.Model((*NamelessModel)(nil)).Table("dynamic_name").CreateTable(nil)
panicIf(err)

row123 := &NamelessModel{
	Id: 123,
}
_, err = db.Model(row123).Table("dynamic_name").Insert()
panicIf(err)

row := new(NamelessModel)
err = db.Model(row).Table("dynamic_name").First()
panicIf(err)
fmt.Println("id is", row.Id)

err = db.Model((*NamelessModel)(nil)).Table("dynamic_name").DropTable(nil)
panicIf(err)

Output:

id is 123

db := modelDB()

book := Book{
	Id:    100,
	Title: "book 100",
}

for i := 0; i < 2; i++ {
	res, err := db.Model(&book).OnConflict("DO NOTHING").Insert()
	if err != nil {
		panic(err)
	}
	if res.RowsAffected() > 0 {
		fmt.Println("created")
	} else {
		fmt.Println("did nothing")
	}
}

err := db.Delete(&book)
if err != nil {
	panic(err)
}

Output:

created
did nothing

db := modelDB()

var book *Book
for i := 0; i < 2; i++ {
	book = &Book{
		Id:    100,
		Title: fmt.Sprintf("title version #%d", i),
	}
	_, err := db.Model(book).
		OnConflict("(id) DO UPDATE").
		Set("title = EXCLUDED.title").
		Insert()
	if err != nil {
		panic(err)
	}

	err = db.Select(book)
	if err != nil {
		panic(err)
	}
	fmt.Println(book)
}

err := db.Delete(book)
if err != nil {
	panic(err)
}

Output:

Book<Id=100 Title="title version #0">
Book<Id=100 Title="title version #1">

db := modelDB()

author := Author{
	Name: "R. Scott Bakker",
}
created, err := db.Model(&author).
	Column("id").
	Where("name = ?name").
	OnConflict("DO NOTHING"). // OnConflict is optional
	Returning("id").
	SelectOrInsert()
if err != nil {
	panic(err)
}
fmt.Println(created, author)

Output:

true Author<ID=2 Name="R. Scott Bakker">

package main

import (
	"fmt"

	"github.com/go-pg/pg"
	"github.com/go-pg/pg/orm"
)

type User struct {
	Id     int64
	Name   string
	Emails []string
}

func (u User) String() string {
	return fmt.Sprintf("User<%d %s %v>", u.Id, u.Name, u.Emails)
}

type Story struct {
	Id       int64
	Title    string
	AuthorId int64
	Author   *User
}

func (s Story) String() string {
	return fmt.Sprintf("Story<%d %s %s>", s.Id, s.Title, s.Author)
}

func main() {
	db := pg.Connect(&pg.Options{
		User: "postgres",
	})
	defer db.Close()

	err := createSchema(db)
	if err != nil {
		panic(err)
	}

	user1 := &User{
		Name:   "admin",
		Emails: []string{"admin1@admin", "admin2@admin"},
	}
	err = db.Insert(user1)
	if err != nil {
		panic(err)
	}

	err = db.Insert(&User{
		Name:   "root",
		Emails: []string{"root1@root", "root2@root"},
	})
	if err != nil {
		panic(err)
	}

	story1 := &Story{
		Title:    "Cool story",
		AuthorId: user1.Id,
	}
	err = db.Insert(story1)
	if err != nil {
		panic(err)
	}

	// Select user by primary key.
	user := &User{Id: user1.Id}
	err = db.Select(user)
	if err != nil {
		panic(err)
	}

	// Select all users.
	var users []User
	err = db.Model(&users).Select()
	if err != nil {
		panic(err)
	}

	// Select story and associated author in one query.
	story := new(Story)
	err = db.Model(story).
		Relation("Author").
		Where("story.id = ?", story1.Id).
		Select()
	if err != nil {
		panic(err)
	}

	fmt.Println(user)
	fmt.Println(users)
	fmt.Println(story)
}

func createSchema(db *pg.DB) error {
	for _, model := range []interface{}{(*User)(nil), (*Story)(nil)} {
		err := db.CreateTable(model, &orm.CreateTableOptions{
			Temp: true,
		})
		if err != nil {
			return err
		}
	}
	return nil
}

Output:

User<1 admin [admin1@admin admin2@admin]>
[User<1 admin [admin1@admin admin2@admin]> User<2 root [root1@root root2@root]>]
Story<1 Cool story User<1 admin [admin1@admin admin2@admin]>>

// Profile belongs to User.
type Profile struct {
	Id     int
	Lang   string
	UserId int
}

type User struct {
	Id      int
	Name    string
	Profile *Profile
}

db := connect()
defer db.Close()

qs := []string{
	"CREATE TEMP TABLE users (id int, name text)",
	"CREATE TEMP TABLE profiles (id int, lang text, user_id int)",
	"INSERT INTO users VALUES (1, 'user 1'), (2, 'user 2')",
	"INSERT INTO profiles VALUES (1, 'en', 1), (2, 'ru', 2)",
}
for _, q := range qs {
	_, err := db.Exec(q)
	if err != nil {
		panic(err)
	}
}

// Select users joining their profiles with following query:
//
// SELECT
//   "user".*,
//   "profile"."id" AS "profile__id",
//   "profile"."lang" AS "profile__lang"
// FROM "users" AS "user"
// LEFT JOIN "profiles" AS "profile" ON "profile"."id" = "user"."profile_id"

var users []User
err := db.Model(&users).
	Column("user.*").
	Relation("Profile").
	Select()
if err != nil {
	panic(err)
}

fmt.Println(len(users), "results")
fmt.Println(users[0].Id, users[0].Name, users[0].Profile)
fmt.Println(users[1].Id, users[1].Name, users[1].Profile)

Output:

2 results
1 user 1 &{1 en 1}
2 user 2 &{2 ru 2}

package main

import (
	"fmt"

	"github.com/go-pg/pg/orm"
)

type InventoryItem struct {
	Name       string
	SupplierID int
	Price      float64
}

type OnHand struct {
	tableName struct{} `sql:"on_hand"`

	Item  InventoryItem `sql:"composite:inventory_item"`
	Count int
}

func main() {
	db := connect()
	defer db.Close()

	err := db.DropTable((*OnHand)(nil), &orm.DropTableOptions{
		IfExists: true,
		Cascade:  true,
	})
	panicIf(err)

	err = db.DropComposite((*InventoryItem)(nil), &orm.DropCompositeOptions{
		IfExists: true,
	})
	panicIf(err)

	err = db.CreateComposite((*InventoryItem)(nil), nil)
	panicIf(err)

	err = db.CreateTable((*OnHand)(nil), nil)
	panicIf(err)

	err = db.Insert(&OnHand{
		Item: InventoryItem{
			Name:       "fuzzy dice",
			SupplierID: 42,
			Price:      1.99,
		},
		Count: 1000,
	})
	panicIf(err)

	onHand := new(OnHand)
	err = db.Model(onHand).Select()
	panicIf(err)

	fmt.Println(onHand.Item.Name, onHand.Item.Price, onHand.Count)
}

Output:

fuzzy dice 1.99 1000

db := modelDB()

count, err := db.Model(&Book{}).Count()
if err != nil {
	panic(err)
}

fmt.Println(count)

Output:

3

db := modelDB()

count, err := db.Model(&Book{}).CountEstimate(0)
if err != nil {
	panic(err)
}

fmt.Println(count)

Output:

3

db := modelDB()

var books []Book
exists, err := db.Model(&books).Where("author_id = ?", 1).Exists()
if err != nil {
	panic(err)
}

fmt.Println(exists)

Output:

true

err := pgdb.Model((*Book)(nil)).
	OrderExpr("id ASC").
	ForEach(func(b *Book) error {
		fmt.Println(b)
		return nil
	})
if err != nil {
	panic(err)
}

Output:

Book<Id=1 Title="book 1">
Book<Id=2 Title="book 2">
Book<Id=3 Title="book 3">

type Profile struct {
	Id     int
	Lang   string
	Active bool
	UserId int
}

// User has many profiles.
type User struct {
	Id       int
	Name     string
	Profiles []*Profile
}

db := connect()
defer db.Close()

qs := []string{
	"CREATE TEMP TABLE users (id int, name text)",
	"CREATE TEMP TABLE profiles (id int, lang text, active bool, user_id int)",
	"INSERT INTO users VALUES (1, 'user 1')",
	"INSERT INTO profiles VALUES (1, 'en', TRUE, 1), (2, 'ru', TRUE, 1), (3, 'md', FALSE, 1)",
}
for _, q := range qs {
	_, err := db.Exec(q)
	if err != nil {
		panic(err)
	}
}

// Select user and all his active profiles with following queries:
//
// SELECT "user".* FROM "users" AS "user" ORDER BY "user"."id" LIMIT 1
//
// SELECT "profile".* FROM "profiles" AS "profile"
// WHERE (active IS TRUE) AND (("profile"."user_id") IN ((1)))

var user User
err := db.Model(&user).
	Column("user.*").
	Relation("Profiles", func(q *orm.Query) (*orm.Query, error) {
		return q.Where("active IS TRUE"), nil
	}).
	First()
if err != nil {
	panic(err)
}
fmt.Println(user.Id, user.Name, user.Profiles[0], user.Profiles[1])

Output:

1 user 1 &{1 en true 1} &{2 ru true 1}

type Item struct {
	Id       int
	Items    []Item `pg:"fk:parent_id"`
	ParentId int
}

db := connect()
defer db.Close()

qs := []string{
	"CREATE TEMP TABLE items (id int, parent_id int)",
	"INSERT INTO items VALUES (1, NULL), (2, 1), (3, 1)",
}
for _, q := range qs {
	_, err := db.Exec(q)
	if err != nil {
		panic(err)
	}
}

// Select item and all subitems with following queries:
//
// SELECT "item".* FROM "items" AS "item" ORDER BY "item"."id" LIMIT 1
//
// SELECT "item".* FROM "items" AS "item" WHERE (("item"."parent_id") IN ((1)))

var item Item
err := db.Model(&item).Column("item.*").Relation("Items").First()
if err != nil {
	panic(err)
}
fmt.Println("Item", item.Id)
fmt.Println("Subitems", item.Items[0].Id, item.Items[1].Id)

Output:

Item 1
Subitems 2 3

type Profile struct {
	Id   int
	Lang string
}

// User has one profile.
type User struct {
	Id        int
	Name      string
	ProfileId int
	Profile   *Profile
}

db := connect()
defer db.Close()

qs := []string{
	"CREATE TEMP TABLE users (id int, name text, profile_id int)",
	"CREATE TEMP TABLE profiles (id int, lang text)",
	"INSERT INTO users VALUES (1, 'user 1', 1), (2, 'user 2', 2)",
	"INSERT INTO profiles VALUES (1, 'en'), (2, 'ru')",
}
for _, q := range qs {
	_, err := db.Exec(q)
	if err != nil {
		panic(err)
	}
}

// Select users joining their profiles with following query:
//
// SELECT
//   "user".*,
//   "profile"."id" AS "profile__id",
//   "profile"."lang" AS "profile__lang",
//   "profile"."user_id" AS "profile__user_id"
// FROM "users" AS "user"
// LEFT JOIN "profiles" AS "profile" ON "profile"."user_id" = "user"."id"

var users []User
err := db.Model(&users).
	Column("user.*").
	Relation("Profile").
	Select()
if err != nil {
	panic(err)
}

fmt.Println(len(users), "results")
fmt.Println(users[0].Id, users[0].Name, users[0].Profile)
fmt.Println(users[1].Id, users[1].Name, users[1].Profile)

Output:

2 results
1 user 1 &{1 en}
2 user 2 &{2 ru}

type Item struct {
	Id    int64
	Attrs map[string]string `sql:",hstore"` // marshalled as PostgreSQL hstore
}

_, err := pgdb.Exec(`CREATE TEMP TABLE items (id serial, attrs hstore)`)
if err != nil {
	panic(err)
}
defer pgdb.Exec("DROP TABLE items")

item1 := Item{
	Id:    1,
	Attrs: map[string]string{"hello": "world"},
}
err = pgdb.Insert(&item1)
if err != nil {
	panic(err)
}

var item Item
err = pgdb.Model(&item).Where("id = ?", 1).Select()
if err != nil {
	panic(err)
}
fmt.Println(item)

Output:

{1 map[hello:world]}

go-pg default convention is that:

• Primary key is called Id, e.g. Model1.Id and Model2.Id.
• Many to many table has columns Model1Id and Model2Id.

If you are not using that convention you have 2 options:

1. Use orm.RegisterTable to register m2m table so go-pg has a chance to adopt to your convention.
2. Use `pg:fk:model2_id,joinFK:model1_id` to specify columns.

package main

import (
	"fmt"

	"github.com/go-pg/pg"
	"github.com/go-pg/pg/orm"
)

func init() {
	// Register many to many model so ORM can better recognize m2m relation.
	// This should be done before dependant models are used.
	orm.RegisterTable((*OrderToItem)(nil))
}

type Order struct {
	Id    int
	Items []Item `pg:"many2many:order_to_items"`
}

type Item struct {
	Id int
}

type OrderToItem struct {
	OrderId int
	ItemId  int
}

func createManyToManyTables(db *pg.DB) error {
	models := []interface{}{
		(*Order)(nil),
		(*Item)(nil),
		(*OrderToItem)(nil),
	}
	for _, model := range models {
		err := db.CreateTable(model, &orm.CreateTableOptions{
			Temp: true,
		})
		if err != nil {
			return err
		}
	}
	return nil
}

// go-pg default convention is that:
//   - Primary key is called Id, e.g. Model1.Id and Model2.Id.
//   - Many to many table has columns Model1Id and Model2Id.
//
// If you are not using that convention you have 2 options:
//  1. Use orm.RegisterTable to register m2m table so go-pg has a chance
//     to adopt to your convention.
//  2. Use `pg:fk:model2_id,joinFK:model1_id` to specify columns.
func main() {
	db := connect()
	defer db.Close()

	if err := createManyToManyTables(db); err != nil {
		panic(err)
	}

	values := []interface{}{
		&Item{Id: 1},
		&Item{Id: 2},
		&Order{Id: 1},
		&OrderToItem{OrderId: 1, ItemId: 1},
		&OrderToItem{OrderId: 1, ItemId: 2},
	}
	for _, v := range values {
		err := db.Insert(v)
		if err != nil {
			panic(err)
		}
	}

	// Select order and all items with following queries:
	//
	// SELECT "order"."id" FROM "orders" AS "order" ORDER BY "order"."id" LIMIT 1
	//
	// SELECT order_to_items.*, "item"."id" FROM "items" AS "item"
	// JOIN order_to_items AS order_to_items ON (order_to_items."order_id") IN (1)
	// WHERE ("item"."id" = order_to_items."item_id")

	order := new(Order)
	err := db.Model(order).Relation("Items").First()
	if err != nil {
		panic(err)
	}
	fmt.Println("Order", order.Id, "Items", order.Items[0].Id, order.Items[1].Id)

	// Select order and all items sorted by id with following queries:
	//
	// SELECT "order"."id" FROM "orders" AS "order" ORDER BY "order"."id" LIMIT 1
	//
	// SELECT order_to_items.*, "item"."id" FROM "items" AS "item"
	// JOIN order_to_items AS order_to_items ON (order_to_items."order_id") IN (1)
	// WHERE ("item"."id" = order_to_items."item_id")
	// ORDER BY item.id DESC

	order = new(Order)
	err = db.Model(order).
		Relation("Items", func(q *orm.Query) (*orm.Query, error) {
			q = q.OrderExpr("item.id DESC")
			return q, nil
		}).
		First()
	if err != nil {
		panic(err)
	}
	fmt.Println("Order", order.Id, "Items", order.Items[0].Id, order.Items[1].Id)

}

Output:

Order 1 Items 1 2
Order 1 Items 2 1

package main

import (
	"fmt"

	"github.com/go-pg/pg"
	"github.com/go-pg/pg/orm"
)

func init() {
	// Register many to many model so ORM can better recognize m2m relation.
	// This should be done before dependant models are used.
	orm.RegisterTable((*ElemToElem)(nil))
}

type Elem struct {
	Id    int
	Elems []Elem `pg:"many2many:elem_to_elems,joinFK:sub_id"`
}

type ElemToElem struct {
	ElemId int
	SubId  int
}

func createManyToManySefTables(db *pg.DB) error {
	models := []interface{}{
		(*Elem)(nil),
		(*ElemToElem)(nil),
	}
	for _, model := range models {
		err := db.CreateTable(model, &orm.CreateTableOptions{
			Temp: true,
		})
		if err != nil {
			return err
		}
	}
	return nil
}

func main() {
	db := connect()
	defer db.Close()

	if err := createManyToManySefTables(db); err != nil {
		panic(err)
	}

	values := []interface{}{
		&Elem{Id: 1},
		&Elem{Id: 2},
		&Elem{Id: 3},
		&ElemToElem{ElemId: 1, SubId: 2},
		&ElemToElem{ElemId: 1, SubId: 3},
	}
	for _, v := range values {
		err := db.Insert(v)
		if err != nil {
			panic(err)
		}
	}

	// Select elem and all subelems with following queries:
	//
	// SELECT "elem"."id" FROM "elems" AS "elem" ORDER BY "elem"."id" LIMIT 1
	//
	// SELECT elem_to_elems.*, "elem"."id" FROM "elems" AS "elem"
	// JOIN elem_to_elems AS elem_to_elems ON (elem_to_elems."elem_id") IN (1)
	// WHERE ("elem"."id" = elem_to_elems."sub_id")

	elem := new(Elem)
	err := db.Model(elem).Relation("Elems").First()
	if err != nil {
		panic(err)
	}
	fmt.Println("Elem", elem.Id)
	fmt.Println("Subelems", elem.Elems[0].Id, elem.Elems[1].Id)
}

Output:

Elem 1
Subelems 2 3

type Example struct {
	Hello string
}

var str sql.NullString
_, err := pgdb.QueryOne(pg.Scan(&str), "SELECT ?hello", &Example{Hello: ""})
if err != nil {
	panic(err)
}
fmt.Println(str.Valid)

Output:

false

type Item struct {
	Id      int64
	Emails  []string `sql:",array"` // marshalled as PostgreSQL array
	Numbers [][]int  `sql:",array"` // marshalled as PostgreSQL array
}

_, err := pgdb.Exec(`CREATE TEMP TABLE items (id serial, emails text[], numbers int[][])`)
panicIf(err)
defer pgdb.Exec("DROP TABLE items")

item1 := Item{
	Id:      1,
	Emails:  []string{"one@example.com", "two@example.com"},
	Numbers: [][]int{{1, 2}, {3, 4}},
}
err = pgdb.Insert(&item1)
panicIf(err)

item := new(Item)
err = pgdb.Model(item).Where("id = ?", 1).Select()
panicIf(err)
fmt.Println(item)

Output:

&{1 [one@example.com two@example.com] [[1 2] [3 4]]}

db := modelDB()

var books []Book
count, err := db.Model(&books).OrderExpr("id ASC").Limit(2).SelectAndCount()
if err != nil {
	panic(err)
}

fmt.Println(count)
fmt.Println(books)

Output:

3
[Book<Id=1 Title="book 1"> Book<Id=2 Title="book 2">]

package main

import (
	"fmt"
	"time"
)

type Flight struct {
	Id        int
	Name      string
	DeletedAt time.Time `pg:",soft_delete"`
}

func main() {
	flight1 := &Flight{
		Id: 1,
	}
	err := pgdb.Insert(flight1)
	panicIf(err)

	// Soft delete.
	err = pgdb.Delete(flight1)
	panicIf(err)

	// Count visible flights.
	count, err := pgdb.Model((*Flight)(nil)).Count()
	panicIf(err)
	fmt.Println("count", count)

	// Count soft deleted flights.
	deletedCount, err := pgdb.Model((*Flight)(nil)).Deleted().Count()
	panicIf(err)
	fmt.Println("deleted count", deletedCount)

	// Actually delete the flight.
	err = pgdb.ForceDelete(flight1)
	panicIf(err)

	// Count soft deleted flights.
	deletedCount, err = pgdb.Model((*Flight)(nil)).Deleted().Count()
	panicIf(err)
	fmt.Println("deleted count", deletedCount)

}

Output:

count 0
deleted count 1
deleted count 0

stmt, err := pgdb.Prepare(`SELECT $1::text, $2::text`)
panicIf(err)

var s1, s2 string
_, err = stmt.QueryOne(pg.Scan(&s1, &s2), "foo", "bar")
panicIf(err)
fmt.Println(s1, s2)

Output:

foo bar

package main

import (
	"fmt"

	"github.com/go-pg/pg"
)

func CreateUser(db *pg.DB, user *User) error {
	_, err := db.QueryOne(user, `
		INSERT INTO users (name, emails) VALUES (?name, ?emails)
		RETURNING id
	`, user)
	return err
}

func GetUser(db *pg.DB, id int64) (*User, error) {
	var user User
	_, err := db.QueryOne(&user, `SELECT * FROM users WHERE id = ?`, id)
	return &user, err
}

func GetUsers(db *pg.DB) ([]User, error) {
	var users []User
	_, err := db.Query(&users, `SELECT * FROM users`)
	return users, err
}

func GetUsersByIds(db *pg.DB, ids []int64) ([]User, error) {
	var users []User
	_, err := db.Query(&users, `SELECT * FROM users WHERE id IN (?)`, pg.In(ids))
	return users, err
}

func CreateStory(db *pg.DB, story *Story) error {
	_, err := db.QueryOne(story, `
		INSERT INTO stories (title, author_id) VALUES (?title, ?author_id)
		RETURNING id
	`, story)
	return err
}

// GetStory returns story with associated author.
func GetStory(db *pg.DB, id int64) (*Story, error) {
	var story Story
	_, err := db.QueryOne(&story, `
		SELECT s.*,
			u.id AS author__id, u.name AS author__name, u.emails AS author__emails
		FROM stories AS s, users AS u
		WHERE s.id = ? AND u.id = s.author_id
	`, id)
	return &story, err
}

func main() {
	db := pg.Connect(&pg.Options{
		User: "postgres",
	})

	err := createSchema(db)
	panicIf(err)

	user1 := &User{
		Name:   "admin",
		Emails: []string{"admin1@admin", "admin2@admin"},
	}
	err = CreateUser(db, user1)
	panicIf(err)

	err = CreateUser(db, &User{
		Name:   "root",
		Emails: []string{"root1@root", "root2@root"},
	})
	panicIf(err)

	story1 := &Story{
		Title:    "Cool story",
		AuthorId: user1.Id,
	}
	err = CreateStory(db, story1)
	panicIf(err)

	user, err := GetUser(db, user1.Id)
	panicIf(err)

	users, err := GetUsers(db)
	panicIf(err)

	story, err := GetStory(db, story1.Id)
	panicIf(err)

	fmt.Println(user)
	fmt.Println(users)
	fmt.Println(story)
}

Output:

User<1 admin [admin1@admin admin2@admin]>
[User<1 admin [admin1@admin admin2@admin]> User<2 root [root1@root root2@root]>]
Story<1 Cool story User<1 admin [admin1@admin admin2@admin]>>

package main

import (
	"fmt"

	"github.com/go-pg/pg"
)

var pgdb *pg.DB

func panicIf(err error) {
	if err != nil {
		panic(err)
	}
}

func main() {
	var user struct {
		Name string
	}

	res, err := pgdb.QueryOne(&user, `
        WITH users (name) AS (VALUES (?))
        SELECT * FROM users
    `, "admin")
	panicIf(err)
	fmt.Println(res.RowsAffected())
	fmt.Println(user)
}

Output:

1
{admin}

_, err := pgdb.Exec(`CREATE TEMP TABLE users(id serial, name varchar(500))`)
panicIf(err)

var user struct {
	Id   int32
	Name string
}
user.Name = "admin"

_, err = pgdb.QueryOne(&user, `
        INSERT INTO users (name) VALUES (?name) RETURNING id
    `, &user)
panicIf(err)
fmt.Println(user)

Output:

{1 admin}

db := txExample()

incrInTx := func(db *pg.DB) error {
	// Transaction is automatically rollbacked on error.
	return db.RunInTransaction(func(tx *pg.Tx) error {
		var counter int
		_, err := tx.QueryOne(
			pg.Scan(&counter), `SELECT counter FROM tx_test FOR UPDATE`)
		if err != nil {
			return err
		}

		counter++

		_, err = tx.Exec(`UPDATE tx_test SET counter = ?`, counter)
		return err
	})
}

var wg sync.WaitGroup
for i := 0; i < 10; i++ {
	wg.Add(1)
	go func() {
		defer wg.Done()
		err := incrInTx(db)
		panicIf(err)
	}()
}
wg.Wait()

var counter int
_, err := db.QueryOne(pg.Scan(&counter), `SELECT counter FROM tx_test`)
panicIf(err)
fmt.Println(counter)

Output:

10

db := modelDB()

book := Book{
	Id: 1,
}
err := db.Select(&book)
if err != nil {
	panic(err)
}
fmt.Println(book)

Output:

Book<Id=1 Title="book 1">

db := modelDB()

var book Book
err := db.Model(&book).Column("book.*").First()
if err != nil {
	panic(err)
}
fmt.Println(book, book.AuthorID)

Output:

Book<Id=1 Title="book 1"> 1

db := modelDB()

var authorId int
var editorId int

filter := func(q *orm.Query) (*orm.Query, error) {
	if authorId != 0 {
		q = q.Where("author_id = ?", authorId)
	}
	if editorId != 0 {
		q = q.Where("editor_id = ?", editorId)
	}
	return q, nil
}

var books []Book
authorId = 1
err := db.Model(&books).
	Apply(filter).
	Select()
if err != nil {
	panic(err)
}
fmt.Println(books)

Output:

[Book<Id=1 Title="book 1"> Book<Id=2 Title="book 2">]

db := modelDB()

var firstBook Book
err := db.Model(&firstBook).First()
if err != nil {
	panic(err)
}
fmt.Println(firstBook)

Output:

Book<Id=1 Title="book 1">

db := modelDB()

var res []struct {
	AuthorId  int
	BookCount int
}

err := db.Model(&Book{}).
	Column("author_id").
	ColumnExpr("count(*) AS book_count").
	Group("author_id").
	OrderExpr("book_count DESC").
	Select(&res)
if err != nil {
	panic(err)
}
fmt.Println("len", len(res))
fmt.Printf("author %d has %d books\n", res[0].AuthorId, res[0].BookCount)
fmt.Printf("author %d has %d books\n", res[1].AuthorId, res[1].BookCount)

Output:

len 2
author 1 has 2 books
author 11 has 1 books

db := modelDB()

var lastBook Book
err := db.Model(&lastBook).Last()
if err != nil {
	panic(err)
}
fmt.Println(lastBook)

Output:

Book<Id=3 Title="book 3">

db := modelDB()

var book Book
err := db.Model(&book).
	Column("book.id", "book.title").
	OrderExpr("book.id ASC").
	Limit(1).
	Select()
if err != nil {
	panic(err)
}

fmt.Println(book)

Output:

Book<Id=1 Title="book 1">

db := modelDB()

var id int
var title string
err := db.Model(&Book{}).
	Column("book.id", "book.title").
	OrderExpr("book.id ASC").
	Limit(1).
	Select(&id, &title)
if err != nil {
	panic(err)
}

fmt.Println(id, title)

Output:

1 book 1

db := modelDB()

var ids []int
err := db.Model(&Book{}).
	ColumnExpr("array_agg(book.id)").
	Select(pg.Array(&ids))
if err != nil {
	panic(err)
}
fmt.Println(ids)

Output:

[1 2 3]

db := modelDB()

var books []Book
err := db.Model(&books).
	WhereGroup(func(q *orm.Query) (*orm.Query, error) {
		q = q.WhereOr("id = 1").
			WhereOr("id = 2")
		return q, nil
	}).
	Where("title IS NOT NULL").
	Select()
if err != nil {
	panic(err)
}
fmt.Println(books)

Output:

[Book<Id=1 Title="book 1"> Book<Id=2 Title="book 2">]

db := modelDB()

var books []Book
err := db.Model(&books).WhereIn("id IN (?)", []int{1, 2}).Select()
if err != nil {
	panic(err)
}
fmt.Println(books)

Output:

[Book<Id=1 Title="book 1"> Book<Id=2 Title="book 2">]

authorBooks := pgdb.Model(&Book{}).Where("author_id = ?", 1)

var books []Book
err := pgdb.Model().
	With("author_books", authorBooks).
	Table("author_books").
	Select(&books)
if err != nil {
	panic(err)
}
fmt.Println(books)

Output:

[Book<Id=1 Title="book 1"> Book<Id=2 Title="book 2">]

// WITH author_books AS (
//     SELECT * books WHERE author_id = 1
// )
// SELECT * FROM author_books
var books []Book
err := pgdb.Model(&books).
	Where("author_id = ?", 1).
	WrapWith("author_books").
	Table("author_books").
	Select(&books)
if err != nil {
	panic(err)
}
fmt.Println(books)

Output:

[Book<Id=1 Title="book 1"> Book<Id=2 Title="book 2">]

db := modelDB()

book := &Book{Id: 1}
err := db.Select(book)
if err != nil {
	panic(err)
}

book.Title = "updated book 1"
err = db.Update(book)
if err != nil {
	panic(err)
}

err = db.Select(book)
if err != nil {
	panic(err)
}

fmt.Println(book)

Output:

Book<Id=1 Title="updated book 1">

db := modelDB()

book1 := &Book{
	Id:        1,
	Title:     "updated book 1",
	UpdatedAt: time.Now(),
}
book2 := &Book{
	Id:        2,
	Title:     "updated book 2",
	UpdatedAt: time.Now(),
}

// UPDATE "books" AS "book"
// SET "title" = _data."title"
// FROM (VALUES ('updated book 1', 1), ('updated book 2', 2)) AS _data("title", "id")
// WHERE "book"."id" = _data."id"
_, err := db.Model(book1, book2).Column("title", "updated_at").Update()
if err != nil {
	panic(err)
}

var books []Book
err = db.Model(&books).Order("id").Select()
if err != nil {
	panic(err)
}

fmt.Println(books)

Output:

[Book<Id=1 Title="updated book 1"> Book<Id=2 Title="updated book 2"> Book<Id=3 Title="book 3">]

db := modelDB()

books := []Book{{
	Id:        1,
	Title:     "updated book 1",
	UpdatedAt: time.Now(),
}, {
	Id:        2,
	Title:     "updated book 2",
	UpdatedAt: time.Now(),
}}

// UPDATE "books" AS "book"
// SET "title" = _data."title"
// FROM (VALUES ('updated book 1', 1), ('updated book 2', 2)) AS _data("title", "id")
// WHERE "book"."id" = _data."id"
_, err := db.Model(&books).Column("title", "updated_at").Update()
if err != nil {
	panic(err)
}

books = nil
err = db.Model(&books).Order("id").Select()
if err != nil {
	panic(err)
}

fmt.Println(books)

Output:

[Book<Id=1 Title="updated book 1"> Book<Id=2 Title="updated book 2"> Book<Id=3 Title="book 3">]

db := modelDB()

book := &Book{
	Id:    1,
	Title: "updated book 1",
}
_, err := db.Model(book).WherePK().UpdateNotNull()
if err != nil {
	panic(err)
}

book = new(Book)
err = db.Model(book).Where("id = ?", 1).Select()
if err != nil {
	panic(err)
}

fmt.Println(book)

Output:

Book<Id=1 Title="updated book 1">

db := modelDB()

var book Book
_, err := db.Model(&book).
	Set("title = concat(?, title, ?)", "prefix ", " suffix").
	Where("id = ?", 1).
	Returning("*").
	Update()
if err != nil {
	panic(err)
}

fmt.Println(book)

Output:

Book<Id=1 Title="prefix book 1 suffix">

db := modelDB()

book := Book{
	Id:       1,
	Title:    "updated book 1", // only this column is going to be updated
	AuthorID: 2,
}
_, err := db.Model(&book).Column("title").WherePK().Returning("*").Update()
if err != nil {
	panic(err)
}

fmt.Println(book, book.AuthorID)

Output:

Book<Id=1 Title="updated book 1"> 1

db := modelDB()

book := Book{
	Id:       1,
	Title:    "updated book 1",
	AuthorID: 2, // this column will not be updated
}
_, err := db.Model(&book).Set("title = ?title").WherePK().Returning("*").Update()
if err != nil {
	panic(err)
}

fmt.Println(book, book.AuthorID)

Output:

Book<Id=1 Title="updated book 1"> 1

package main

import (
	"time"

	"github.com/go-pg/pg"
)

var pgdb *pg.DB

func panicIf(err error) {
	if err != nil {
		panic(err)
	}
}

func main() {
	var count int
	// Use bigger timeout since this query is known to be slow.
	_, err := pgdb.WithTimeout(time.Minute).QueryOne(pg.Scan(&count), `
		SELECT count(*) FROM big_table
	`)
	panicIf(err)
}

Output: