I have a table in Postgresql called 'transaction'. The primary key of this table is composite with (id, date), where id is an int and date is a timestamp. Every day, the first order starts at order #1. The next order that day would be #2, then #3, and so on until the next day with #1 again.

I'm not sure how I would change this SQL query to find the max transaction ID specifically from today then increment 1 to it.. or start again at 1 if there are no transactions today. So far the only thing I can do is just start at 1 if there are no transactions at all, or increment based on the highest ID across all dates.

/* 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;
}

CodePudding user response：

You need to update your sub-select

select ifnull(max(id), 0)   1 from pos_schema.transaction

to something like

SELECT
    ifnull(max(id), 0)   1
FROM pos_schema.transaction
WHERE
    pos_schema.transactiondate.date::date = CURRENT_DATE
    

Please note that your field date should really be of type date instead of timestamp. Otherwise your primary key does not protect you from inserting two duplicate IDs for the same date if they have different timestamps.