Skip to content

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