canonical / canonical/sqlair

ERROR: column \"sqlair_0\" does not exist (SQLSTATE 42703) when using sqlair with postgres

Open
#189 2 comments 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
19
Forks
10
PR merge metrics
No merged PRs in 30d

Description

## 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

```go
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()
}
```

```shell
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
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.