qtableview fails to display the newly inserted row

When using QtableView + QSqlTableModel combination, QtableView fails to properly display the inserted row after insertion of a new one.

The id field is hidden. The EditStrategy is OnFieldChange. There is a button to insert a new empty row at the end of the model. After the user fills in all fields and hits enter or clicks on other rows, the expected behavior is for the row to be submitted to the database and the view keeping the new row. However, the QSqlTableModel succeeds to insert the data into the database table, but the tableview's newly inserted row becomes empty and fields uneditable.

The behavior changes to the desired one if the id field is not hidden and is explicitly provided. Yet if the id is not hidden and not populated manually, the behaviour fails again as mentioned above.

Why this happens?

Here is the minimum reproducible code. You will have to change database configuration fileds. And connect to the table with autoincrement id:

import sys

from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtCore import QModelIndex
from PyQt5.QtSql import QSqlDatabase, QSqlTableModel
from PyQt5.QtWidgets import QPushButton


class Ui_main(object):
    def setupUi(self, main):
        main.setObjectName("main")
        main.resize(781, 652)
        main.setContextMenuPolicy(QtCore.Qt.NoContextMenu)

        self.verticalLayoutWidget = QtWidgets.QWidget(main)
        self.verticalLayoutWidget.setGeometry(QtCore.QRect(10, 10, 761, 631))
        self.verticalLayoutWidget.setObjectName("verticalLayoutWidget")
        self.verticalLayout = QtWidgets.QVBoxLayout(self.verticalLayoutWidget)
        self.verticalLayout.setContentsMargins(0, 0, 0, 0)
        self.verticalLayout.setObjectName("verticalLayout")

        # Replace values with your database configurations
        # But the table you refer to must have autoincremented primary key
        database = QSqlDatabase.addDatabase('QPSQL')
        database.setHostName('localhost')
        database.setPort(5432)
        database.setDatabaseName('some_database')
        database.setUserName('some_user')
        database.setPassword('some_password')
        database.open()

        button_add = QPushButton("AddRow")
        button_add.clicked.connect(self.addRow)
        self.verticalLayout.addWidget(button_add)

        self.tableView = QtWidgets.QTableView(self.verticalLayoutWidget)
        self.tableView.setObjectName("tableView")
        self.tableView.verticalHeader().setVisible(False)
        self.verticalLayout.addWidget(self.tableView)

        self.table_model = QSqlTableModel(self.tableView, database)
        table_name = 'some_table'
        self.table_model.setTable(table_name)

        self.table_model.setEditStrategy(QSqlTableModel.OnFieldChange)
        self.table_model.select()

        self.tableView.setModel(self.table_model)
        self.tableView.hideColumn(0)

        self.retranslateUi(main)
        QtCore.QMetaObject.connectSlotsByName(main)

    def retranslateUi(self, main):
        _translate = QtCore.QCoreApplication.translate
        main.setWindowTitle(_translate("main", "main"))

    def addRow(self):
        count = self.table_model.rowCount(QModelIndex())
        self.table_model.insertRow(count)
        self.tableView.scrollToBottom()

if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    main_window = QtWidgets.QWidget()
    window = Ui_main()
    window.setupUi(main_window)
    main_window.show()
    sys.exit(app.exec_())

Update:

The temporary workaround I am using right now is finding max id from the fetched table and append max_id + 1 to the newly inserted row. Thus, the row has an id on submission to the database. However, this seems to be not the right way as the database handles primary keys itself...

Update 2: Here is how my database table looks like:

Design:

book_of_accounts: table
+ columns
    id: bigint NN default nextval('book_of_accounts_id_seq'::regclass)
    code: varchar(256) NN
    name: varchar(1024) NN
    account_type: varchar(256) NN
    quantitative: boolean NN
    monetary: boolean NN
    subconoto_1: varchar(256)
    subconoto_2: varchar(256)
    subconoto_3: varchar(256)
+ indices
    book_of_accounts_pkey: unique (id)
    bookofaccounts_code: unique (code)
    bookofaccounts_name: index (name)
+ keys
    book_of_accounts_pkey: PK (id)

SQL

create table if not exists book_of_accounts
(
    id bigserial not null
        constraint book_of_accounts_pkey
        primary key,
    code varchar(256) not null,
    name varchar(1024) not null,
    account_type varchar(256) not null,
    quantitative boolean not null,
    monetary boolean not null,
    subconoto_1 varchar(256),
    subconoto_2 varchar(256),
    subconoto_3 varchar(256)
);

alter table book_of_accounts owner to hamlet;

create unique index if not exists bookofaccounts_code
    on book_of_accounts (code);

create index if not exists bookofaccounts_name
    on book_of_accounts (name);