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

It's showing up as "information_schema.sql_identifer" for the type for the table names. I was looking to make it a string. — SouthernSleuth
– SouthernSleuth, Commented Apr 1, 2020 at 18:21
That's the type internal to PostgreSQL, right? It's right there in the docs. So what's the type GORM maps it to? I've assumed from your question that unmarshaling of the query results into a slice of strings, []string, fails with some sort of an error about type mismatch, but you did not tell us which exactly for some reason. Is this true? — kostix
– kostix, Commented Apr 2, 2020 at 10:00
Please make us not excercise in "guesseneering"; if an alleged solution fails, the question should include exact error messges. Please see this. — kostix
– kostix, Commented Apr 2, 2020 at 10:01
That is not how Gorm works. You might want to check the db.Pluck function. — Dat Ngo
– Dat Ngo, Commented Apr 2, 2020 at 14:21

Dat Ngo · Accepted Answer · 2020-04-03 08:27:44Z

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

Will · Accepted Answer · 2024-01-20 01:38:56Z

0

And if you're trying to do this with sqllite like I was, the following code will do it

var tables []string
if err := logic.DB().Table("sqlite_master").Where("type = ?", "table").Pluck("name", &tables).Error; err != nil {
    panic(err)
}

answered Jan 20, 2024 at 1:38

Will

6,2964 gold badges33 silver badges49 bronze badges

Collectives™ on Stack Overflow

Using GORM to retrieve tables names from Postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related