Pyqt6 bug with QSqlRelationalTableModel and Postgresql (version 13)

Question

The following code works perfectly with Sqlite3 but not with Postgres. No bug but the returned table is always empty...

self.model = QSqlRelationalTableModel(db=db)        
self.model.setTable("hr_employee")
self.model.setRelation(2, QSqlRelation("Job", "job_id", "name"))
self.model.select()
self.table.setModel(self.model)

if I remove this line, it works !

self.model.setRelation(2, QSqlRelation("Job", "job_id", "name"))

I would appreciate any help because I am stuck.

Answer 1

I bumped into this bug while following a PyQt6 book tutorial as well.

I run postgres 15 under the hood similar to your settings. So after a few hours of googling and trying, I finally get it to display joined tables by doing the following:

1. rename all tables in your database to lower case names
2. rename all columns in each table to lower case
3. adjust your QSqlRelation definition to reflect these changes

I didn't know what the exact cause for this incompatibility with Postgres but from my other development experience with psql, i assume it's because psql is quite unfriendly with capital letters and in many cases it does lower case conversion automatically when you refer to tables/columns (basically you have to wrap names by double quotes to explicitly tell psql the name is referred to as is, otherwise psql implicitly convert it to lower cases).

I can only assume the developer of PyQt6 didn't handle such cases in their code either.