How to `CREATE DATABASE dbName IF NOT EXISTS` in postgres in golang

If you're here, it's probably because you're having trouble using SQL for postgres in Golang to create a database if it doesn't exist. Part of the problem is, postgres doesn't support the CREATE DATABASE dbName IF NOT EXISTS; idiom for databases. Here's how I'm doing it:

statement := `SELECT EXISTS(SELECT datname FROM pg_catalog.pg_database WHERE datname = 'yourDBName');`

row := db.QueryRow(statement)
var exists bool
err = row.Scan(&exists)
check(err)

if exists == false {
    statement = `CREATE DATABASE yourDBName;`
    _, err = db.Exec(statement)
    check(err)
}

pg_catalog.pg_database is where postgres keeps metadata about your databases. datname is a column in pg_database. More info on them here. Don't change datname. For a while I was dumb and thought it should be substituted with my database name. I guess we learn.

statement returns true or false depending on whether your database exists. If you run this statement in psql, you'll see it's returned on a single row. This is important because you can grab it with the next few lines.

db.Queryrow() returns a single row of an SQL query. row.Scan() copies the result into the (memory location of) exists variable.

Now you can create a database if exists is false.

I went through a lot of solutions online, including this one but none of them really worked for me. I like my solution because it's explicit and I can see everything going on.

Comments

Comment deleted 2 months, 2 weeks ago

New Comment

required

required (not published)

optional

required