Mismatch in primary key types should not cause a failure on SQL writing - Githubissues

frictionlessdata / tableschema-sql-py

Generate SQL tables, load and extract data, based on JSON Table Schema descriptors.

MIT License

61 stars 19 forks source link

Mismatch in primary key types should not cause a failure on SQL writing #87

Open OriHoch opened 7 years ago

OriHoch commented 7 years ago

reproduction

table schema:

{"fields": [
{"name": "id", "type": "integer"},
{"name": "date", "type": "string"}
],
"primaryKey": ["id"]

write some rows to a PostgreSQL DB - but in the rows, have the id as type string (but numeric)
data in DB is inserted correctly, and DB converts it to integer
now, write the same rows again

expected

provide an error message, warning about type mismatch
or match rows in DB (as opposed to bloom)

actual

writer.__prepare_bloom - collects all data as integers
writer.__check_existing - compares with a string id - which doesn't match
this causes insert
when inserting, the DB matches the primary key (even though types don't match)
error about duplicate key

OriHoch commented 7 years ago

see: https://github.com/frictionlessdata/datapackage-pipelines/issues/77

roll commented 7 years ago

Thank for reporting @OriHoch (cc @akariv)