Pg: How do I create an index

[mailbaoer]

I can't find any doc said about how to create an index with pg.Haven't support it?

[vmihailenco]

No, go-pg does support creating table schemas and/or indexes. I usually create schema and indexes manually:

db.Exec(`
CREATE TABLE (...);
CREATE INDEX ...;
`)

[mailbaoer]

that's a pity :sob:

[saginadir]

If it helps someone, I am able to auto generate an index on a column when creating my tables with the following code:

Please let me know if you have a better solution ❤️

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

        // Extract the table name from the model
        var tableName string
        _, err := db.Model(model).Query(&tableName, "SELECT '?TableName'")
        if err != nil {
            panic(err)
        }
        // Trim the quotes which by default will look like -> "table_name"
        tableName = tableName[1 : len(tableName)-1]

        // Create an index on "deleted_at" column of each table to support the soft delete functionality of the BaseModel
        _, err = db.Exec(fmt.Sprintf("CREATE INDEX %s_deleted_at ON %s (deleted_at)", tableName, tableName))
        if err != nil {
            panic(err)
        }
    }
    return nil
}

[sgon00]

@saginadir thank you very much for sharing your code. Based on your code, I created mine and works very well. The following is my code:

type Base struct {
    ID        int64
    CreatedAt time.Time `sql:"default:now()"`
    UpdatedAt time.Time `sql:"default:now()"`
}

// BeforeUpdate ... 
func (b *Base) BeforeUpdate(ctx context.Context) (context.Context, error) {
    b.UpdatedAt = time.Now()
    return ctx, nil
}

var _ orm.BeforeUpdateHook = (*Base)(nil)

// Order ...
type Order struct {
    Base
    Name  string
    Items []Item `pg:"many2many:order_to_items"`
}

// Item ...
type Item struct {
    Base
    Name string
}

// OrderToItem ...
type OrderToItem struct {
    OrderID int64
    ItemID  int64
}

func createSchema(db *pg.DB) error {
    // Register many to many model so ORM can better recognize m2m relation.
    orm.RegisterTable((*OrderToItem)(nil))

    models := []interface{}{
        &Order{},
        &Item{},
        &OrderToItem{},
    }

    // Drop Tables For Testing (Don't do it in production)
    err := DropTable(db, models)
    if err != nil {
        return err
    }

    // Create Tables (will create created_at index automatically)
    err = CreateTable(db, models)
    if err != nil {
        return err
    }

    err = CreateIndex(db, (*OrderToItem)(nil), "btree", "order_id")
    if err != nil {
        return err
    }
    err = CreateIndex(db, (*OrderToItem)(nil), "btree", "item_id")
    if err != nil {
        return err
    }

    return nil
}

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

        // Create btree index on created_at column if exists
        if reflect.ValueOf(model).Elem().FieldByName("CreatedAt").IsValid() {
            err := CreateIndex(db, model, "btree", "created_at")
            if err != nil {
                return err
            }
        }
    }

    return nil
}

// DropTable ...
func DropTable(db *pg.DB, models []interface{}) error {
    for _, model := range models {
        err := db.DropTable(model, &orm.DropTableOptions{
            IfExists: true,
        })
        if err != nil {
            return err
        }
    }

    return nil
}

// GetTableName ...
func GetTableName(db *pg.DB, model interface{}) (string, error) {
    var tableName string
    // Extract the table name from the model
    _, err := db.Model(model).Query(&tableName, "SELECT '?TableName'")
    if err != nil {
        return "", err
    }

    // I am paranoid and check if tableName is quoted string
    if tableName[0:1] != `"` || tableName[len(tableName)-1:len(tableName)] != `"` {
        return "", errors.New("tableName format returned from postgres is not correct")
    }
    // Trim the quotes which by default will look like -> "table_name"
    tableName = tableName[1 : len(tableName)-1]
    return tableName, nil
}

// CreateIndex ...
func CreateIndex(db *pg.DB, model interface{}, indexType string, indexColumn string) error {
    tableName, err := GetTableName(db, model)
    if err != nil {
        return err
    }

    _, err = db.Model(model).Exec(
        fmt.Sprintf("CREATE INDEX IF NOT EXISTS %s_%s_idx ON %s USING %s (%s)", tableName, indexColumn, tableName, indexType, indexColumn),
    )
    if err != nil {
        return err
    }
    return nil
}

[sgon00]

Hi, just FYI. Hope it can help others. I improved my index creation version by creating custom tags for example `cpg:"index:btree"`. The following is the new code. (btw Underscore() function is copied from go-pg/pg/v9/internal)

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

type Story struct {
    ID             int64
    Title         string
    AuthorID int64 `cpg:"index:btree"`
    Author   *User
}

func createSchema(db *pg.DB) error {
    models := []interface{}{
        &User{},
        &Story{},
    }

    // Create Tables
    err = CreateTable(db, models)
    if err != nil {
        return err
    }

    return nil
}

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

        err = handleTagCpg(db, model, nil)
        if err != nil {
            return err
        }
    }

    return nil
}

// rootModel is for handling nested anonymous structs inside a table struct
func handleTagCpg(db *pg.DB, model interface{}, rootModel interface{}) error {
    if rootModel == nil {
        rootModel = model
    }
    t := reflect.TypeOf(model).Elem()
    for i := 0; i < t.NumField(); i++ {
        field := t.Field(i)
        if field.Anonymous {
            err := handleTagCpg(db, reflect.New(field.Type).Interface(), rootModel)
            if err != nil {
                return err
            }
            continue
        }
        tag := field.Tag.Get("cpg")
        if tag != "" {
            tagSplit := strings.Split(tag, ":")
            if tagSplit[0] == "index" {
                err := CreateIndex(db, rootModel, tagSplit[1], Underscore(field.Name))
                if err != nil {
                    return err
                }
            }
        }
    }
    return nil
}

// Underscore converts "CamelCasedString" to "camel_cased_string".
func Underscore(s string) string {
    r := make([]byte, 0, len(s)+5)
    for i := 0; i < len(s); i++ {
        c := s[i]
        if IsUpper(c) {
            if i > 0 && i+1 < len(s) && (IsLower(s[i-1]) || IsLower(s[i+1])) {
                r = append(r, '_', ToLower(c))
            } else {
                r = append(r, ToLower(c))
            }
        } else {
            r = append(r, c)
        }
    }
    return string(r)
}

// IsUpper ...
func IsUpper(c byte) bool {
    return c >= 'A' && c <= 'Z'
}

// IsLower ...
func IsLower(c byte) bool {
    return c >= 'a' && c <= 'z'
}

// ToUpper ...
func ToUpper(c byte) byte {
    return c - 32
}

// ToLower ...
func ToLower(c byte) byte {
    return c + 32
}

// GetTableName ...
func GetTableName(db *pg.DB, model interface{}) (string, error) {
    var tableName string
    // Extract the table name from the model
    _, err := db.Model(model).Query(&tableName, "SELECT '?TableName'")
    if err != nil {
        return "", err
    }

    // I am paranoid and check if tableName is quoted string
    if tableName[0:1] != `"` || tableName[len(tableName)-1:len(tableName)] != `"` {
        return "", errors.New("tableName format returned from postgres is not correct")
    }
    // Trim the quotes which by default will look like -> "table_name"
    tableName = tableName[1 : len(tableName)-1]
    return tableName, nil
}

// CreateIndex ...
func CreateIndex(db *pg.DB, model interface{}, indexType string, indexColumn string) error {
    tableName, err := GetTableName(db, model)
    if err != nil {
        return err
    }

    _, err = db.Model(model).Exec(
        fmt.Sprintf("CREATE INDEX IF NOT EXISTS %s_%s_idx ON %s USING %s (%s)", tableName, indexColumn, tableName, indexType, indexColumn),
    )
    if err != nil {
        return err
    }
    return nil
}

[vmihailenco]

That is a pity you all have to write custom code, but with tool like https://github.com/go-pg/migrations creating an index is a matter of creating a file 123_create_index.up.sql with contents like:

create index concurrently your_index_name on your_table_name (foo, bar);

And you have all the power of SQL with support of all index types PostgreSQL has in its arsenal... I just don't see what assistance go-pg can provide here...

[saginadir]

That is a pity you all have to write custom code, but with tool like https://github.com/go-pg/migrations creating an index is a matter of creating a file 123_create_index.up.sql with contents like:

create index concurrently your_index_name on your_table_name (foo, bar);

And you have all the power of SQL with support of all index types PostgreSQL has in its arsenal... I just don't see what assistance go-pg can provide here...

@vmihailenco First of all, thank you for your hard work on go-pg, the tool has been valuable to me as I'm sure it's valuable to others as well.

But regarding your suggestion, I don't think you see the issue we are trying to tackle. The module you've provided in your suggestion is NOT what I was looking for. If I've wanted something similar I could have easily picked one of the dozens of similar tools written in various other languages.

What I am looking for is a tool that will just enable me to create an index on an already existing db.CreateTable functionality without creating additional files to support that. What I was deeply missing in my case - is the ability to enable the soft delete functionality, you see, soft delete will work terribly with an unindexed "deleted_at" column. If I would have left it to me or my employees to maintain a separate "migrations" directory with up/down files, we would have surely gotten to a situation where one of our API endpoints is clogging our entire database just because the "deleted_at" column is not indexed.

We are not asking for a complete indexing solution that will provide features like a support for multi-column indexes - but having the ability to just simply add a tag sql:",index" will be magical and will cover most use-cases.

In fact, it seems like our buddy @sgon00 has already created a solution for that. So I assume, and of course if you also think it'll be useful, adding this option to the core of go-pg won't cause too much trouble. I'll be happy to assist with the PR if it's something you will welcome.

[Routhinator]

@vmihailenco is this still true in v10? Sorry, I'm brand new to Golang and just wading in here as a v10 first timer, and you now have https://pkg.go.dev/github.com/go-pg/pg/v10#example-DB.Model-CreateTable - which I've worked my app init around, however if I can't mark the index on the struct that defines a table, that might be a show stopper for that very handy utility....

[saginadir]

@vmihailenco is this still true in v10? Sorry, I'm brand new to Golang and just wading in here as a v10 first timer, and you now have https://pkg.go.dev/github.com/go-pg/pg/v10#example-DB.Model-CreateTable - which I've worked my app init around, however if I can't mark the index on the struct that defines a table, that might be a show stopper for that very handy utility....

@Routhinator if you are new to Golang perhpas try the Gorm ORM, it supports more feature and it's better documented. go-pg is lower level and as you see it's missing indexes tags ;)