PostgreSQL+Qt+C++实现当日交易ID自增起始为1的方案问询

编辑说明：以下原查询无法正常运行，但核心思路仍可参考。如需修复查询，可使用PostgreSQL中替代SQLite IFNULL()的函数（如COALESCE()）

PostgreSQL中有一张名为transaction的表，复合主键为(id, date)，其中id为整数类型，date为时间戳类型。需求是：每天的第一笔订单编号从1开始，当日后续订单依次递增为2、3……次日重新从1开始。

目前仅能实现两种情况：要么无任何交易时从1开始，要么基于所有日期的最高ID递增。需要修改现有SQL查询，使其获取当日的最大交易ID并加1；若当日无交易，则从1开始。

原代码实现 #

/* Insert Transaction */
bool dbmanager::addTransaction(const int& custPhone=0, const int& totalCents=0, const qstr& items="",
                               const qstr& paymentType="", const int& tender=0, const int& change=0,
                               const int& cardNum=0, const int& cardExp=0, const int& cardCVV=0, QWidget* from=nullptr)
{
    QSqlQuery q;
    // TODO: This query increments the largest order number that exists across all dates. Make it so the order number is 1+ the max order number from today
    q.prepare("insert into pos_schema.transaction values( (select ifnull(max(id), 0) + 1 from pos_schema.transaction), NOW(), "
              ":phone, :total_cents, :items, :payment_type, :tender, :change, :card_number, :card_exp, :card_cvv);");
    q.bindValue(":phone",custPhone);
    q.bindValue(":total_cents", totalCents);
    q.bindValue(":items", items);
    q.bindValue(":payment_type", paymentType);
    q.bindValue(":tender", tender);
    q.bindValue(":change", change);


    QString cryptCardNum = crypt.encryptToString(qstr::number(cardNum));
    QString cryptCardExp = crypt.encryptToString(qstr::number(cardExp));
    QString cryptCardCVV = crypt.encryptToString(qstr::number(cardCVV));

    q.bindValue(":card_number", cryptCardNum);
    q.bindValue(":card_exp", cryptCardExp);
    q.bindValue(":card_cvv", cryptCardCVV);

    if (q.exec())
        return true;

    qDebug() << "Transaction Insertion Error:" << q.lastError().text();
    displayError("Insertion", from, q);
    return false;
}

核心修改点 #

• 限定当日数据：在查询max(id)时，通过date_trunc('day', date) = date_trunc('day', NOW())筛选出当日的交易记录，确保只统计当天的订单编号。
• 替换空值处理函数：PostgreSQL不支持SQLite的IFNULL()，改用COALESCE()函数——当当日无交易时，max(id)返回NULL，COALESCE将其替换为0，加1后得到起始值1。

修改后的SQL语句 #

将原代码中的q.prepare内的SQL替换为：

insert into pos_schema.transaction 
values( 
    (select COALESCE(max(id), 0) + 1 from pos_schema.transaction where date_trunc('day', date) = date_trunc('day', NOW())), 
    NOW(), 
    :phone, :total_cents, :items, :payment_type, :tender, :change, :card_number, :card_exp, :card_cvv
);

并发场景提示 #

如果存在多请求同时插入的并发场景，上述写法可能出现主键冲突问题。更安全的实现方式可以是：

• 使用SELECT ... FOR UPDATE锁定当日的查询结果，避免并发插入冲突；
• 或者创建每日序列号表，结合触发器实现每日自增逻辑；
• 也可以利用PostgreSQL的序列功能，按日期分组生成自增ID。

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