Overview
Hello, I have been using sqlair with sqlite quite a bit and I'm trying to use it with postgres using the pgx driver. I am not able to write data to the database using sqlair. I consistently get the following error: ERROR: column \"sqlair_0\" does not exist (SQLSTATE 42703). Am I doing something wrong? Is sqlair not supporting pgx/postgres? Some help here would be welcome.
Example Code
package db
import (
"context"
"database/sql"
"fmt"
"github.com/canonical/sqlair"
_ "github.com/jackc/pgx/v5/stdlib"
)
type Database struct {
conn *sqlair.DB
}
type Config struct {
Host string
Port int
User string
Password string
DBName string
}
func New(config *Config) (*Database, error) {
connString := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", config.Host, config.Port, config.User, config.Password, config.DBName)
sqlConnection, err := sql.Open("pgx", connString)
if err != nil {
return nil, err
}
_, err = sqlConnection.Exec(fmt.Sprintf(QueryCreateNewsletterSubscribersTable, NewsletterSubscribersTableName))
if err != nil {
_ = sqlConnection.Close()
return nil, fmt.Errorf("unable to create newsletter subscribers table: %w", err)
}
if _, err := sqlConnection.Exec(fmt.Sprintf(QueryCreateUsersTable, UsersTableName)); err != nil {
return nil, err
}
return &Database{
conn: sqlair.NewDB(sqlConnection),
}, nil
}
func (db *Database) Close() error {
if db.conn == nil {
return nil
}
return db.conn.PlainDB().Close()
}
const NewsletterSubscribersTableName = "newsletter_subscribers"
const QueryCreateNewsletterSubscribersTable = `CREATE TABLE IF NOT EXISTS %s (
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
status TEXT NOT NULL DEFAULT 'pending'
);`
const (
createNewsletterSubscriberStmt = "INSERT INTO %s (email, status) VALUES ($NewsletterSubscriber.email, $NewsletterSubscriber.status)"
)
type NewsletterSubscriber struct {
ID int64 `db:"id"`
Email string `db:"email"`
Status string `db:"status"`
}
func (db *Database) CreateNewsletterSubscriber(ctx context.Context, email string) error {
stmt := fmt.Sprintf(createNewsletterSubscriberStmt, tbl(NewsletterSubscribersTableName))
q, err := sqlair.Prepare(stmt, NewsletterSubscriber{})
if err != nil {
return err
}
ns := NewsletterSubscriber{Email: email}
return db.conn.Query(ctx, q, ns).Run()
}guillaume@courge:~/code/ellanetworks.com$ go run cmd/ellanetworks/main.go --config config.yaml
2025-08-21T09:47:13-04:00 fatal ellanetworks/main.go:47 Couldn't create newsletter subscriber {"component": "Ella", "error": "ERROR: column \"sqlair_0\" does not exist (SQLSTATE 42703)"}
exit status 1
Overview
Hello, I have been using sqlair with sqlite quite a bit and I'm trying to use it with postgres using the pgx driver. I am not able to write data to the database using sqlair. I consistently get the following error:
ERROR: column \"sqlair_0\" does not exist (SQLSTATE 42703). Am I doing something wrong? Is sqlair not supporting pgx/postgres? Some help here would be welcome.Example Code