Hacker News Reader

Comment by crazygringo | Hacker News Reader

Wow, it never even occurred to me a database's columns might not have fixed types, but that each value could have its own type.

As someone else in the comments linked to their FAQ, they say it's not a bug but a feature.

But... when would that ever be a feature?

Also, how on earth does indexing on a column work, or heck even a sort? However they define it must be a gigantic headache... so I'm curious what possibly motivated this "feature"?

View on HN

At the time sqlite was written, the author of sqlite was heavily involved with tcl, a dynamicly typed programming language. Sqlite also started out as a tcl extension. Having the database match the typing philosophy of the language it was supposed to be thightly integrated with probably seemed like a good idea.

One nice thing that sqlite being dynamicly typed allows for, is having a key-value table for storing singleton objects, like project config (as opposed to having a well defined table with a single row).

> One nice thing that sqlite being dynamicly typed allows for, is having a key-value table for storing singleton objects,

OK I can see how this can be convenient. But I would prefer a special-case "variant" column type for such cases and otherwise default to strongly typed columns.

> One nice thing that sqlite being dynamicly typed allows for, is having a key-value table for storing singleton objects, like project config (as opposed to having a well defined table with a single row).

If you need to store singleton objects, wouldn't you just serialize manually to a string representation and store them as TEXT? A fancier solution would be something like the JSON type in Postgres.

I fail to see how sqlite's dynamic types are helping at all in this scenario.

No need to serialize first?

(edit) And deserialize again on read.

(edit) Storing project config in Postgres, even though i like it very much, sounds like overkill, excuse the strong words.

Numeric data may be missing for any number of reasons. Storing the reason, as opposed to just null, could be useful.

Courageous to post this here, but I emphatically disagree. Any aggregation operation will effectively have undefined behavior. You could make it like a spreadsheet where cells have both numeric data (either static or derived as in the case of a cell formula that sums things up, as an example) or functions, but if you want row/cell metadata per entry then that should be a new database type or some kind of extension.

If I have a column that's defined (lol... hinted) as an INT then if I see a STRING or TEXT in one of the rows I'm gonna be pretty pissed.

But it's ok, at least this is an exciting novelty to observe and shake one's head at.

Creating a mapping between primitive data type and "kind of content" is ad-hoc, arbitrary, and icky. What if you want your reasons to be numeric as well, then the scheme falls apart? What about aggregates, as a sibling comment mentioned?

Copy of another comment of mine: I dealt with a large sqlite database once where a past bug had entered data of a different data type. Instead of blowing up when the rows were created or modified, it blowed up in a much harder to debug way much, much later.

I'm skeptical of using different types in a dynamically typed language (or db) as a tagged union where each type has a different meaning. IMHO youre better off with two columns with a constraint that exactly one of them must be Null. The issue is someone at some point is going to write code reasonably assuming the value must be an integer, not a string, and when the value turns out to be a string you'll get unpredictable behavior.

That sounds like a nightmare to deal with. You can't use any aggregate as it's either not well defined (e.g. sum) or will be wrong (e.g. count). Kust put the reason in another column if it's needed.

Reply on news.ycombinator.com