Writing queries¶

Design¶

Our goal is to help you write SQL, not to hide or replace it with custom dialect. go-pg query builder helps with:

splitting long queries into logically separated blocks;
replacing ? placeholders with properly escaped values;
generating list of columns and some joins from Go models.

For example, the following Go code:

err := db.Model(book).
    ColumnExpr("lower(name)").
    Where("? = ?", pg.Ident("id"), "some-id").
    Select()

generates the following query:

SELECT lower(name)
FROM "books"
WHERE "id" = 'some-id'

Select¶

SQL	go-pg
SELECT col1, col2	Column("col1", "col2")
SELECT col1, col2	ColumnExpr("col1, col2")
SELECT count()	ColumnExpr("count()")
SELECT count("id")	ColumnExpr("count(?)", pg.Ident("id"))
FROM "table1", "table2"	Table("table1", "table2")
FROM table1, table2	TableExpr("table1, table2")
JOIN table1 ON col1 = 'value1'	Join("JOIN table1 ON col1 = ?", "value1")
JOIN table1 ON col1 = 'value1'	Join("JOIN table1").JoinOn("col1 = ?", "value1")
LEFT JOIN table1 ON col1 = 'value1'	Join("LEFT JOIN table1 ON col1 = ?", "value1")
WHERE id = 1	Where("id = ?", 1)
WHERE "foo" = 'bar'	Where("? = ?", pg.Ident("foo"), "bar")
WHERE id = 1 OR foo = 'bar'	Where("id = ?", 1).WhereOr("foo = ?", "bar")
GROUP BY "col1", "col2"	Group("col1", "col2")
GROUP BY col1, col2	GroupExpr("col1, col2")
GROUP BY "col1", "col2"	GroupExpr("?, ?", pg.Ident("col1"), pg.Ident("col2"))
ORDER BY "col1" ASC	Order("col1 ASC")
ORDER BY col1 ASC	OrderExpr("col1 ASC")
ORDER BY "col1" ASC	OrderExpr("? ASC", pg.Ident("col1"))
LIMIT 10	Limit(10)
OFFSET 1000	Offset(1000)

Select book by primary key:

book := new(Book)
err := db.Model(book).Where("id = ?", 1).Select()

SELECT "book"."id", "book"."title", "book"."text"
FROM "books" WHERE id = 1

Select only book title and text:

err := db.Model(book).
    Column("title", "text").
    Where("id = ?", 1).
    Select()

SELECT "title", "text" FROM "books" WHERE id = 1

Select only book title and text into variables:

var title, text string
err := db.Model((*Book)(nil)).
    Column("title", "text").
    Where("id = ?", 1).
    Select(&title, &text)

SELECT "title", "text"
FROM "books" WHERE id = 1

Select book using WHERE ... AND ...:

err := db.Model(book).
    Where("id > ?", 100).
    Where("title LIKE ?", "my%").
    Limit(1).
    Select()

SELECT "book"."id", "book"."title", "book"."text"
FROM "books"
WHERE (id > 100) AND (title LIKE 'my%')
LIMIT 1

Select book using WHERE ... OR ...:

err := db.Model(book).
    Where("id > ?", 100).
    WhereOr("title LIKE ?", "my%").
    Limit(1).
    Select()

SELECT "book"."id", "book"."title", "book"."text"
FROM "books"
WHERE (id > 100) OR (title LIKE 'my%')
LIMIT 1

Select book user WHERE ... AND (... OR ...):

err := db.Model(book).
    Where("title LIKE ?", "my%").
    WhereGroup(func(q *pg.Query) (*pg.Query, error) {
        q = q.WhereOr("id = 1").
            WhereOr("id = 2")
        return q, nil
    }).
    Limit(1).
    Select()

SELECT "book"."id", "book"."title", "book"."text"
FROM "books"
WHERE (title LIKE 'my%') AND (id = 1 OR id = 2)
LIMIT 1

Select first 20 books:

var books []Book
err := db.Model(&books).Order("id ASC").Limit(20).Select()

SELECT "book"."id", "book"."title", "book"."text"
FROM "books"
ORDER BY id ASC LIMIT 20

Count books:

count, err := db.Model((*Book)(nil)).Count()

SELECT count(*) FROM "books"

Select 20 books and count all books:

count, err := db.Model(&books).Limit(20).SelectAndCount()

SELECT "book"."id", "book"."title", "book"."text"
FROM "books" LIMIT 20;

SELECT count(*) FROM "books";

Select 20 books and count estimated number of books:

count, err := db.Model(&books).Limit(20).SelectAndCountEstimate(100000)

SELECT "book"."id", "book"."title", "book"."text"
FROM "books" LIMIT 20;

EXPLAIN SELECT '_go_pg_placeholder' FROM "books"
SELECT count(*) FROM "books";

Select author ID and number of books:

var res []struct {
    AuthorId  int
    BookCount int
}
err := db.Model((*Book)(nil)).
    Column("author_id").
    ColumnExpr("count(*) AS book_count").
    Group("author_id").
    Order("book_count DESC").
    Select(&res)

SELECT "author_id", count(*) AS book_count
FROM "books" AS "book"
GROUP BY author_id
ORDER BY book_count DESC

Select book IDs as PostgreSQL array:

var ids []int
err := db.Model((*Book)(nil)).ColumnExpr("array_agg(id)").Select(pg.Array(&ids))

SELECT array_agg(id) FROM "books"

Select by multiple ids:

ids := []int{1, 2, 3}
err := db.Model((*Book)(nil)).
    Where("id in (?)", pg.In(ids)).
    Select()

SELECT * FROM books WHERE id IN (1, 2, 3)

Select books for update

book := &Book{}
err := db.Model(book).
    Where("id = ?", 1).
    For("UPDATE").
    Select()

SELECT * FROM books WHERE id  = 1 FOR UPDATE

CTE¶

Select books using WITH statement:

authorBooks := db.Model((*Book)(nil)).Where("author_id = ?", 1)

err := db.Model().
    With("author_books", authorBooks).
    Table("author_books").
    Select(&books)

WITH "author_books" AS (
  SELECT "book"."id", "book"."title", "book"."text"
  FROM "books" AS "book" WHERE (author_id = 1)
)
SELECT * FROM "author_books"

Same query using WrapWith:

err := db.Model(&books).
    Where("author_id = ?", 1).
    WrapWith("author_books").
    Table("author_books").
    Select(&books)

WITH "author_books" AS (
  SELECT "book"."id", "book"."title", "book"."text"
  FROM "books" AS "book" WHERE (author_id = 1)
)
SELECT * FROM "author_books"

Subqueries¶

Subquery in FROM:

authorBooks := db.Model((*Book)(nil)).Where("author_id = ?", 1)

err := db.Model().TableExpr("(?) AS book", authorBooks).Select(&books)

SELECT * FROM (
  SELECT "book"."id", "book"."title", "book"."text"
  FROM "books" AS "book" WHERE (author_id = 1)
) AS book

Subquery in WHERE:

authorBooks := db.Model((*Book)(nil)).ColumnExpr("id").Where("author_id = ?", 1)

err := db.Model(&books).Where("id IN (?)", authorBooks).Select()

SELECT * FROM "books" WHERE id IN (
  SELECT id FROM "books" AS "book" WHERE (author_id = 1)
)

Column names¶

Select book and associated author:

err := db.Model(book).Relation("Author").Select()

SELECT
  "book"."id", "book"."title", "book"."text",
  "author"."id" AS "author__id", "author"."name" AS "author__name"
FROM "books"
LEFT JOIN "users" AS "author" ON "author"."id" = "book"."author_id"
WHERE id = 1

Select book ID and the associated author id:

err := db.Model(book).Column("book.id").Relation("Author.id").Select()

SELECT "book"."id", "author"."id" AS "author__id"
FROM "books"
LEFT JOIN "users" AS "author" ON "author"."id" = "book"."author_id"
WHERE id = 1

Select book and join author without selecting it:

err := db.Model(book).Relation("Author._").Select()

SELECT "book"."id"
FROM "books"
LEFT JOIN "users" AS "author" ON "author"."id" = "book"."author_id"
WHERE id = 1

Join and select book author without selecting book:

err := db.Model(book).
    Column("_").
    Relation("Author").
    Where("id = ?", 1).
    Select()

SELECT "author"."id" AS "author__id", "author"."name" AS "author__name"
FROM "books"
LEFT JOIN "users" AS "author" ON "author"."id" = "book"."author_id"
WHERE id = 1

Insert¶

Insert struct¶

Insert new book returning primary keys:

err := db.Model(book).Insert()

INSERT INTO "books" (title, text) VALUES ('my title', 'my text') RETURNING "id"

Insert new book returning all columns:

err := db.Model(book).Returning("*").Insert()

INSERT INTO "books" (title, text) VALUES ('my title', 'my text') RETURNING *

Insert new book or update existing one:

_, err := db.Model(book).
    OnConflict("(id) DO UPDATE").
    Set("title = EXCLUDED.title").
    Insert()

INSERT INTO "books" ("id", "title") VALUES (100, 'my title')
ON CONFLICT (id) DO UPDATE SET title = 'title version #1'

Insert slice¶

Insert slice in a single query:

books := []*Book{book1, book2}
_, err := db.Model(&books).Insert()

INSERT INTO "books" (title, text) VALUES ('title1', 'text2'), ('title2', 'text2') RETURNING "id"

Alternatively:

_, err := db.Model(book1, book2).Insert()

INSERT INTO "books" (title, text) VALUES ('title1', 'text2'), ('title2', 'text2') RETURNING *

Insert map¶

Insert map[string]interface{}:

values := map[string]interface{}{
    "title": "title1",
    "text":  "text1",
}
_, err := db.Model(&values).TableExpr("books").Insert()

INSERT INTO books (title, text) VALUES ('title1', 'text2')

Insert from Select¶

selq := db.Model((*Book)(nil)).
    Where("1 = 1")

_, err := db.Model((*Book)(nil)).
    With("sel", selq).
    TableExpr("sel").
    Insert()

WITH "sel" AS (
  SELECT "book"."id"
  FROM "books" AS "book"
  WHERE (1 = 1)
)
INSERT INTO "books"
SELECT * FROM sel

Select or insert¶

Select existing book by name or create new book:

_, err := db.Model(book).
    Where("title = ?title").
    OnConflict("DO NOTHING"). // optional
    SelectOrInsert()

SELECT * FROM "books" WHERE title = 'my title';

INSERT INTO "books" (title, text) VALUES ('my title', 'my text') RETURNING "id";

Update¶

Update struct¶

Update all columns except primary keys:

book := &Book{
    ID:    1,
    Title: "my title",
    Text:  "my text",
}
err := db.Model(book).WherePK().Update()

UPDATE books SET title = 'my title', text = 'my text' WHERE id = 1

Update only column "title":

res, err := db.Model(book).Set("title = ?title").Where("id = ?id").Update()

UPDATE books SET title = 'my title' WHERE id = 1

Update only column "title":

res, err := db.Model(book).
    Column("title").
    Where("id = ?", 1).
    Update()

UPDATE books SET title = 'my title' WHERE id = 1

Upper column "title" and scan it:

var title string
res, err := db.Model(book).
    Set("title = upper(title)").
    Where("id = ?", 1).
    Returning("title").
    Update(&title)

UPDATE books SET title = upper(title) WHERE id = 1 RETURNING title

Update slice¶

Update multiple books with single query:

err := db.Model(book1, book2).WherePK().Update()

UPDATE books AS book SET title = _data.title, text = _data.text
FROM (VALUES (1, 'title1', 'text1'), (2, 'title2', 'text2')) AS _data (id, title, text)
WHERE book.id = _data.id

Update map¶

Update map[string]interface{}:

values := map[string]interface{}{
    "title": "title1",
    "text":  "text1",
}
_, err := db.Model(&values).TableExpr("books").Where("id = ?", 1).Update()

UPDATE books SET title = 'title1', text = 'text2' WHERE id = 1

Delete¶

Delete book by primary key:

res, err := db.Model(book).Where("id = ?", 1).Delete()

DELETE FROM "books" WHERE id = 1

Delete book by title:

res, err := db.Model(book).Where("title = ?title").Delete()

DELETE FROM "books" WHERE title = 'my title'

Delete multiple books using ids:

res, err := db.Model((*Book)(nil)).
    Where("id IN (?)", pg.In([]int{1, 2})).
    Delete()

DELETE FROM "books" WHERE id IN (1, 2)

Delete multiple books using structs:

books := []*Book{book1, book2} // slice of books with ids

res, err := db.Model(&books).WherePK().Delete()

DELETE FROM "books" WHERE id IN (1, 2)

Joins¶

Select a book and manually join the book author:

book := new(Book)
err := db.Model(book).
    ColumnExpr("book.*").
    ColumnExpr("a.id AS author__id, a.name AS author__name").
    Join("JOIN authors AS a ON a.id = book.author_id").
    First()

SELECT book.*, a.id AS author__id, a.name AS author__name
FROM books
JOIN authors AS a ON a.id = book.author_id
ORDER BY id LIMIT 1

Join conditions can be split using JoinOn:

q.Join("LEFT JOIN authors AS a").
    JoinOn("a.id = book.author_id").
    JoinOn("a.active = ?", true)

You can achieve the same using ORM relations which are described below.

Executing custom queries¶

Create index for the table:

_, err := db.Model((*Book)(nil)).Exec(`
    CREATE INDEX CONCURRENTLY books_author_id_idx
    ON ?TableName (author_id)
`)

Or:

var count int
_, err := db.Model((*Book)(nil)).QueryOne(pg.Scan(&count), `
    SELECT count(*)
    FROM ?TableName AS ?TableAlias
`)