使用Go与sqlx操作PostgreSQL时遭遇pq语法错误的问题排查

问题 #

我创建了如下PostgreSQL表：

CREATE TABLE "order"
(
    "order" jsonb NOT NULL
);

尝试使用sqlx通过以下代码操作数据库：

func (r *RepositoryPostgres) CreateDocument(order L0.Order) error {
    tx := r.db.MustBegin()

    orderJSON, err := json.Marshal(order.OrderEntry)
    if err != nil {
        return err
    }

    tx.MustExec("INSERT INTO order (order) VALUES ($1)", orderJSON)
    err = tx.Commit()

    if err != nil {
        return err
    }
    return nil
}

func (r *RepositoryPostgres) GetAllDocuments() ([]L0.Order, error) {
    var documents []L0.Order

    err := r.db.Select(&documents, "SELECT * FROM order")
    if err != nil {
        return nil, err
    }

    return documents, nil
}

但遇到错误：pq: syntax error at or near "order"，请问语法存在什么问题？

错误原因与解决办法 #

order是PostgreSQL的保留关键字。你创建表和字段时用双引号包裹了它，让数据库将其视为普通标识符，但后续SQL语句中未加双引号，数据库会把order解析为关键字，从而触发语法错误。

解决方法是在所有引用该表名和字段名的SQL语句中，用双引号包裹order：

修正后的CreateDocument函数 #

func (r *RepositoryPostgres) CreateDocument(order L0.Order) error {
    tx := r.db.MustBegin()

    orderJSON, err := json.Marshal(order.OrderEntry)
    if err != nil {
        return err
    }

    // 给表名和字段名添加双引号
    tx.MustExec("INSERT INTO \"order\" (\"order\") VALUES ($1)", orderJSON)
    err = tx.Commit()

    if err != nil {
        return err
    }
    return nil
}

修正后的GetAllDocuments函数 #

func (r *RepositoryPostgres) GetAllDocuments() ([]L0.Order, error) {
    var documents []L0.Order

    // 给表名添加双引号
    err := r.db.Select(&documents, "SELECT * FROM \"order\"")
    if err != nil {
        return nil, err
    }

    return documents, nil
}

另外建议尽量避免使用数据库保留关键字作为表名或字段名，比如将表名改为orders、字段名改为order_data，能从根源避免这类语法问题。

内容的提问来源于stack exchange，提问作者Neighbourhood