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

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

Output:

-1

package main

import (
	"fmt"

	"github.com/go-pg/pg/v10"
	"github.com/go-pg/pg/v10/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 `pg:"rel:has-one"`
}

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",
		Password: "postgres",
	})
	defer db.Close()

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

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

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

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

	// Select user by primary key.
	user := &User{Id: user1.Id}
	err = db.Model(user).WherePK().Select()
	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)
}

// createSchema creates database schema for User and Story models.
func createSchema(db *pg.DB) error {
	models := []interface{}{
		(*User)(nil),
		(*Story)(nil),
	}

	for _, model := range models {
		err := db.Model(model).CreateTable(&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 `pg:"rel:belongs-to"`
}

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",
//   "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 1}
2 user 2 &{2 ru 2}

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()

book1 := &Book{
	Title: "new book 1",
}
book2 := &Book{
	Title: "new book 2",
}
_, err := db.Model(book1, book2).Insert()
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.Model(&books).Insert()
if err != nil {
	panic(err)
}
fmt.Println(books)

Output:

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

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">]

package main

import (
	"fmt"

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

type InventoryItem struct {
	Name       string
	SupplierID int
	Price      float64
}

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

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

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

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

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

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

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

	_, err = db.Model(&OnHand{
		Item: InventoryItem{
			Name:       "fuzzy dice",
			SupplierID: 42,
			Price:      1.99,
		},
		Count: 1000,
	}).Insert()
	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

type Model1 struct {
	Id int
}

type Model2 struct {
	Id   int
	Name string

	Model1Id int     `pg:"on_delete:RESTRICT,on_update: CASCADE"`
	Model1   *Model1 `pg:"rel:has-one"`
}

for _, model := range []interface{}{&Model1{}, &Model2{}} {
	err := pgdb.Model(model).CreateTable(&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}]

package main

import (
	"fmt"
	"time"

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

const pgTimeFormat = "15:04:05.999999999"

type Time struct {
	time.Time
}

var _ types.ValueAppender = (*Time)(nil)

func (tm Time) AppendValue(b []byte, flags int) ([]byte, error) {
	if flags == 1 {
		b = append(b, '\'')
	}
	b = tm.UTC().AppendFormat(b, pgTimeFormat)
	if flags == 1 {
		b = append(b, '\'')
	}
	return b, nil
}

var _ types.ValueScanner = (*Time)(nil)

func (tm *Time) ScanValue(rd types.Reader, n int) error {
	if n <= 0 {
		tm.Time = time.Time{}
		return nil
	}

	tmp, err := rd.ReadFullTemp()
	if err != nil {
		return err
	}

	tm.Time, err = time.ParseInLocation(pgTimeFormat, string(tmp), time.UTC)
	if err != nil {
		return err
	}

	return nil
}

type Event struct {
	Id   int
	Time Time `pg:"type:time"`
}

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

	err := db.Model((*Event)(nil)).CreateTable(&orm.CreateTableOptions{
		Temp: true,
	})
	panicIf(err)

	_, err = db.Model(&Event{
		Time: Time{time.Date(0, 0, 0, 12, 00, 00, 00, time.UTC)}, // noon
	}).Insert()
	panicIf(err)

	evt := new(Event)
	err = db.Model(evt).Select()
	panicIf(err)

	fmt.Println(evt.Time)
}

Output:

0000-01-01 12:00:00 +0000 UTC

db := modelDB()

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

_, err = db.Model(book).WherePK().Delete()
if err != nil {
	panic(err)
}

err = db.Model(book).WherePK().Select()
fmt.Println(err)

Output:

pg: no rows in result set

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

type Model1 struct {
}

var model1 Model1
_, err := pgdb.QueryOne(&model1, "SELECT 1 AS id")
fmt.Printf("Model1: %v\n", err)

type Model2 struct {
	tableName struct{} `pg:",discard_unknown_columns"`
}

var model2 Model2
_, err = pgdb.QueryOne(&model2, "SELECT 1 AS id")
fmt.Printf("Model2: %v\n", err)

Output:

Model1: pg: can't find column=id in model=Model1 (prefix the column with underscore or use discard_unknown_columns)
Model2: <nil>

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 `pg:"rel:has-many"`
}

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 *pg.Query) (*pg.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:"rel:has-many,join_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 `pg:"rel:has-one"`
}

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"
// 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}
2 user 2 &{2 ru}

type Item struct {
	Id    int64
	Attrs map[string]string `pg:",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.Model(&item1).Insert()
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]}

db := modelDB()

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

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

Output:

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

type NamelessModel struct {
	tableName struct{} `pg:"_"` // "_" 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.Model(book).WherePK().Delete()
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.Model(book).WherePK().Select()
	if err != nil {
		panic(err)
	}
	fmt.Println(book)
}

_, err := db.Model(book).WherePK().Delete()
if err != nil {
	panic(err)
}

Output:

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

package main

import (
	"fmt"

	"github.com/go-pg/pg/v10"
	"github.com/go-pg/pg/v10/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 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.Model(v).Insert()
		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 *pg.Query) (*pg.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)

}

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

Output:

Order 1 Items 1 2
Order 1 Items 2 1

package main

import (
	"fmt"

	"github.com/go-pg/pg/v10"
	"github.com/go-pg/pg/v10/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,join_fk: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.Model(model).CreateTable(&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.Model(v).Insert()
		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 `pg:",array"` // marshalled as PostgreSQL array
	Numbers [][]int  `pg:",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{"[email protected]", "[email protected]"},
	Numbers: [][]int{{1, 2}, {3, 4}},
}
_, err = pgdb.Model(item1).Insert()
panicIf(err)

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

Output:

&{1 [[email protected] [email protected]] [[1 2] [3 4]]}

db := modelDB()

book := &Book{
	ID: 1,
}
err := db.Model(book).WherePK().Select()
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 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">]

db := modelDB()

var authorId int
var editorId int

filter := func(q *pg.Query) (*pg.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()

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">

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 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 books []Book
err := db.Model(&books).
	WhereGroup(func(q *pg.Query) (*pg.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">]

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

err := pgdb.Model((*Flight)(nil)).CreateTable(&orm.CreateTableOptions{
	Temp: true,
})
panicIf(err)

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

// Soft delete.
_, err = pgdb.Model(flight1).WherePK().Delete()
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.Model(flight1).WherePK().ForceDelete()
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

package main

import (
	"fmt"
	"time"

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

type CustomTime struct {
	Time time.Time
}

var _ types.ValueScanner = (*CustomTime)(nil)

func (tm *CustomTime) ScanValue(rd types.Reader, n int) error {
	var err error
	tm.Time, err = types.ScanTime(rd, n)
	return err
}

var _ types.ValueAppender = (*CustomTime)(nil)

func (tm *CustomTime) AppendValue(b []byte, flags int) ([]byte, error) {
	return types.AppendTime(b, tm.Time, flags), nil
}

type Video struct {
	Id        int
	Name      string
	DeletedAt CustomTime `pg:",soft_delete"`
}

func main() {
	video1 := &Video{
		Id: 1,
	}
	_, err := pgdb.Model(video1).Insert()
	panicIf(err)

	// Soft delete.
	_, err = pgdb.Model(video1).WherePK().Delete()
	panicIf(err)

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

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

	// Actually delete the video.
	_, err = pgdb.Model(video1).WherePK().ForceDelete()
	panicIf(err)

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

}

Output:

count 0
deleted count 1
deleted count 0

db := modelDB()

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

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

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

fmt.Println(book)

Output:

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

db := modelDB()

book := &Book{
	ID:    1,
	Title: "updated book 1",
}
_, err := db.Model(book).WherePK().UpdateNotZero()
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

type Event struct {
	ID     int
	Active bool `pg:",use_zero"`
}

db := pg.Connect(pgOptions())
defer db.Close()

err := db.Model((*Event)(nil)).CreateTable(&orm.CreateTableOptions{
	Temp: true,
})
if err != nil {
	panic(err)
}

event := &Event{
	ID:     1,
	Active: true,
}
_, err = db.Model(event).Insert()
if err != nil {
	panic(err)
}

fmt.Println(event)

event.Active = false
_, err = db.Model(event).WherePK().UpdateNotZero()
if err != nil {
	panic(err)
}

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

fmt.Println(event2)

Output:

&{1 true}
&{1 false}

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/v10"
)

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",
		Password: "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]>>

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 rolled back on error.
	return db.RunInTransaction(ctx, 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

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: