4 ms·

Doesn't this convert all data to strings? I think that makes some queries wonky. Or does it search through each row and see that one element out of 1000 intege

Doesn't this convert all data to strings? I think that makes some queries wonky. Or does it search through each row and see that one element out of 1000 integers was a float, so the whole column should be floats?

jitl 7y ago
All the data in a CSV is already strings.
da_chicken 7y ago
The format is .import CSVFILE TABLENAME [0]. If TABLENAME already exists, it would use that schema. Otherwise it will all be TEXT data using the first row as column names. However, SQLite uses dynamic typing (they call it "type affinity") so it kind of doesn't matter. I like SQLite a lot, but one of the funniest things about it is how they try to sell you that type affinity is a great thing [1] when really it's not at all what you want in 99% of RDBMS situations. There's a reason that everybody from MySQL and PostgreSQL to MS SQL Server, Oracle, and DB2 use static typing. [0]: https://sqlite.org/cli.html#csv_import https://sqlite.org/cli.html#csv_import [1]: https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
- 6thaccount2 7y ago
  Will doing a where clause (ex: where column_x > 3) actually work if column_x is now all strings? I recall my query executing and returning incorrect results. I figured out it would work if I explicitly caste the where clause as ( where float(column_x) > 3) or something like that (this was a few years ago). Edit: I assume most databases like Oracle (it's SQL packages looked bizzare to me until I started learning Ada and figured out it has the same syntax) use static typing for performance, query optimization, and error checking? Note that although I use SQL almost daily, I am no expert on the innards. SQLite has a different use case. It seems to be for embedded and ad-hoc analysis where dynamic typing makes more sense. This is just a guess though.

4 ms·

Doesn't this convert all data to strings? I think that makes some queries wonky. Or does it search through each row and see that one element out of 1000 intege

by 6thaccount2 7y ago

jitl 7y ago
All the data in a CSV is already strings.
da_chicken 7y ago
The format is .import CSVFILE TABLENAME [0]. If TABLENAME already exists, it would use that schema. Otherwise it will all be TEXT data using the first row as column names. However, SQLite uses dynamic typing (they call it "type affinity") so it kind of doesn't matter. I like SQLite a lot, but one of the funniest things about it is how they try to sell you that type affinity is a great thing [1] when really it's not at all what you want in 99% of RDBMS situations. There's a reason that everybody from MySQL and PostgreSQL to MS SQL Server, Oracle, and DB2 use static typing. [0]: https://sqlite.org/cli.html#csv_import https://sqlite.org/cli.html#csv_import [1]: https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
- 6thaccount2 7y ago
  Will doing a where clause (ex: where column_x > 3) actually work if column_x is now all strings? I recall my query executing and returning incorrect results. I figured out it would work if I explicitly caste the where clause as ( where float(column_x) > 3) or something like that (this was a few years ago). Edit: I assume most databases like Oracle (it's SQL packages looked bizzare to me until I started learning Ada and figured out it has the same syntax) use static typing for performance, query optimization, and error checking? Note that although I use SQL almost daily, I am no expert on the innards. SQLite has a different use case. It seems to be for embedded and ad-hoc analysis where dynamic typing makes more sense. This is just a guess though.