Using GORM to retrieve tables names from Postgresql

Question

Looking to retrieve table names from my postgresql database. Now, I know in Go you can use sql and the pq driver, but I'm using GORM for doing queries in my REST API.

The table_name type in PostgreSQL is "information_schema.sql_identifier". This is what I was trying to do, but the type isn't string.

var tables []string
if err := db.Table("information_schema.tables").Select("table_name").Where("table_schema = ?", "public").Find(&tables).Error; err != nil {
    panic(err)
}

Answer 1

TL;DR

To select a single column values into a slice using Gorm, you can use db.Pluck helper:

var tables []string
if err := db.Table("information_schema.tables").Where("table_schema = ?", "public").Pluck("table_name", &tables).Error; err != nil {
    panic(err)
}

TS;WM

Considering this, the SELECT statement returns a set of rows with one or more columns. In order to map those to Go code, we need a sort of struct so that Gorm can understand which column is mapped to which field of the struct. Even when you only select 1 single column, it's just a struct with 1 single field.

type Table struct {
    TableName   string
    // more fields if needed...
}

So your output variable should be []*Table:

var tables []*Table
if err := db.Table("information_schema.tables").Select("table_name").Where("table_schema = ?", "public").Find(&tables).Error; err != nil {
    panic(err)
}

Note: it could be []Table as well if you don't want to modify the element inside the slice.

If you don't want to define the struct, you can use the the db.Pluck function which is just a helper of this sort of code:

rows, err := db.Table("information_schema.tables").Select("table_name").Where("table_schema = ?", "public").Rows()
defer rows.Close()

var tables []string
var name string
for rows.Next() {
    row.Scan(&name)
    tables = append(tables, name)
}