Looks like this question here addresses the exact same problem, only for scala spark. I'll definitely try the solution to let Postgres handle string types as unspecified. This post here at Databricks translates the solution to pyspark, reproducing it here:

df.write.format("postgresql").mode("overwrite").option("truncate", "true").option("stringtype", "unspecified").option("dbtable", table).option("host", host).option("database", database).option("user", username).option("password", password).save()

Apparently it's also important to use the postgresql driver, not the jdbc driver. Will try that inside glue ...