My external disk is APFS formatted, has been since I set it up last year, and I'm having this problem when attempting to import both JPG and PNG images, though I haven't tried any other formats. Sonoma 14.5, M1 Air 2020.
So no, it's not an exFAT matter, or not exclusively an exFAT matter.
However, the disk is also encrypted via the standard macOS tools in Disk Utility (password required on disk mount), and it's been partitioned to do 50/50 duty as a Time Machine volume, so it is arguably no longer a plain-vanilla APFS volume.
If the only way to get a photos library to function properly on an external volume is to disable its macOS-provided encryption, or to use only unpartitioned media, this is a matter that Apple needs to address.
I have not attempted reformatting, nor do I intend to. Apple isn't Microsoft. The solution is not "format and reinstall".