> But then why not have SQLITE_INTEGER64 to indicate that the value is 64 bit integer? I feel like the entire idea I tried to explain must have not been understood, which points to a serious failing in explanatory power from me. I will try one more time, hoping I do a better job this time, but first, it is a real good idea to heed Larry's advice and read up on what he suggested, which should also clarify some of what I am about to say. ## TLDR: SQLite Storage is Typeless; The data in the database isn't regarded (by the database itself) as having any significance. It doesn't know that a value is perhaps someone's Age, or the name of a Song, it just accepts what you put in there and gives it back when needed. You might think "Yes - that's obvious, what a silly example", and you would be right, it is both silly and obvious but it also is exactly the same principle that underlies ALL storage in SQLite. SQLite not only doesn't know that a value is someone's age, it doesn't even know that the value is specifically an Integer, or Int64, or string or any of that, to the database engine, it is just a bunch of bytes. (I'll steal Keith's favorite term here and call it a "bag 'o bytes" from now on). Try it yourself, make a Table with a column of type "NUMERIC", then INSERT into it a value but bind that with sqlite3_bind_int() (or int64 or FLoat if you like) then read it back out in a select query and use the sqlite3_column_text() - it all works because SQLite doesn't know and doesn't care what is in your bag 'o bytes when it stores it, though it may [treat the bytes differently based on how you bind it](https://sqlite.org/c3ref/bind_blob.html). ## How it works It's like when you have some "stuff" you want to put into storage and you come to the storage facility and say, "Hey people, can you please store my stuff? I'll pay". Being a nice capitalist storage facility, they might say "Yes sure" and then, "Ok, so for us to store it, you need to bring it in a nice storage container." You might then say "But what kind of container?". They may answer: "Well it's your choice really, but we do offer these 2feet x 1feet boxes, or if that is too small, we have the bigger 4ft x 8ft containers, or if you need bigger, we also have full size shipping containers. You might then say "Oh good, my "stuff" is sort of small sized, so I will bring it to store in the 2x1ft container ok?" The facility then says "Perfect, we will put it away." Internally though, they cheat to save money and are not using the full containers, but they pack the stuff out of the container and pack it together as tightly as possible with whatever other stuff is there already from other people, simply marking clearly where your stuff begins and ends. When you go back later and say "Please can I have my stuff?" They say "sure", because they know exactly where it is, but now they forgot what container it was brought in with, so they quickly make up a story and say "You know, as an added service we offer repacking stuff into whatever container you like, at no extra charge". You, being impressed with this service, then says "Excellent, I will take it in the 4x8ft container." They promptly stuff your stuff into that size container and hand it back to you, and everyone is happy. ## Please note: - At no point in this story did the container size matter very much to either You or the storage facility, it was just whatever was convenient to transfer the stuff in, though the facility did offer a few specific options they have as a standard to transfer "stuff" in. - The facility never knew or cared what "stuff" you had, or even what "Type" of "stuff" it was. ## Now back to SQLite: When you hand off "stuff" to SQLite it doesn't care what it is, to the DB engine it is just a bag of stuff, or specifically because we are computing, a "bag 'o bytes". It may be handled differently in queries based on the column's "Affinity", but that has nothing to do with the specific stored value. It offers some containers to transfer your bag 'o bytes into the engine with, namely the sqlite3_bind...() interfaces which can handle bytes as Integers, Int64's, Text, IEEE754 Floats, etc, and even a completely untyped container that can transfer bags 'o bytes directly (binary large objects, or BLOBs for short). Similarly, upon retrieval, it will offer several ways to transfer the bytes back to you. The sqlite_column...() suite of functions will give your bytes back as Ints, int64s, Text, etc. whatever is needed by YOU, so you decide the method. ## Now to refer back to your question: > But then why not have SQLITE_INTEGER64 to indicate that the value is 64 bit integer? Hopefully now you can see that this question is moot. SQLite doesn't know anything about what "type" your data is, and cannot "indicate" it to you in any way, YOU have to know what type it is, and more importantly, what Type of container you need it to be delivered in. SQLite will deliver your bytes in whatever way you ask for it. It's just bytes. I hope it makes more sense this time. :)