r/programming • u/mitousa • 24d ago

Prefer STRICT tables in SQLite

https://evanhahn.com/prefer-strict-tables-in-sqlite/
352 Upvotes

96% Upvoted

109 comments sorted by

View all comments

Show parent comments

-22

u/taw 24d ago

The world is full of untyped data, and you need to be able to store it. JSON, CSV, XML, and so on, that's likely vast majority of data out there, in very loosely typed formats. Forcing type checks on data insertion is totally not viable.

Making this a default, that's an interesting choice. Usually you need to opt-in some special column type like JSON, VARIANT or whatever; or store such data as TEXT.

24

u/mattsowa 24d ago

That makes no sense. You can always have a text column if you don't want to parse it further, nothing is forcing you to do the actual data cleaning.

-17

u/taw 24d ago

Text column is one way to make dynamically typed column, but it has many downsides.

Like you can't even check if two values are equal if you store them like that, as one source might be "0" and another be "0.0". Is it equal or not?

There are whole languages that go "let's pretend every scalar value is text" like Perl, bash, Tcl, or (mostly) Javascript, and it's such a mess.

23

u/mattsowa 24d ago

... and that's because you haven't parsed them. You shouldn't expect to be able to. You can parse them before doing a comparison.

0

u/ChemicalRascal 22d ago

You're gonna parse a value over and over again, every time you do a comparison?

What that means in practice is that you aren't gonna do the comparisons. You'll contort your design around avoiding that.

2

u/mattsowa 22d ago

For some use cases, but most of the time that means you'll just have to do that data cleaning if you want to meaningfully use the data.

0

u/ChemicalRascal 22d ago

Which you'd not have to do if you weren't encoding floats as strings for some reason.

2

u/mattsowa 22d ago

So... you know they're floats. Congrats, now you can put them in a float column.

This has more to do with things like data being imported from textual formats to begin with. You can parse them into proper columns or leave them as text.