SQLite seems to support far fewer types than PostgreSQL. Is that a limitation of its design, or a deliberate tradeoff?
Why does SQLite have fewer data types than PostgreSQL?
Answers
A lot of it comes down to different goals. SQLite is an embedded, serverless database designed to be small, dependable, and easy to ship without a separate database service. Every built-in type brings rules for storage, conversion, comparison, and compatibility that need to be maintained.
PostgreSQL makes a different tradeoff. Its richer type system can enforce more meaning and behavior inside the database, which is especially useful when different applications or clients share the same data.
Reply to this answer
The big distinction is that SQLite normally associates a type with the value, not rigidly with the column. Its storage classes are NULL, INTEGER, REAL, TEXT, and BLOB. A declared column type usually sets an affinity, which influences conversions rather than guaranteeing that every value has that type.
So a date can be stored as text or a numeric value, for example, and a boolean can be represented by an integer. Your application and constraints need to agree on the representation. That is different from having a dedicated database type with its own rules.
Tivnelo+5q:
Does that mean SQLite can't enforce column types at all?
Mivnaro-7c:
It can. STRICT tables, available since SQLite 3.37, enforce a limited set of column types. They don't add PostgreSQL's full range of types, though.
Reply to this answer
Think about where you want the rules to live. With an embedded database, an application can handle some validation and conversion itself. That can be convenient, but if several clients write to the same database, relying on each one to interpret values the same way can get messy. Database constraints and STRICT tables can help.
Reply to this answer
Having fewer storage classes doesn't mean you can only model five kinds of data. You can represent more complicated values using those building blocks. The tradeoff is that encoding a value yourself isn't the same as getting dedicated type validation and operations from the database.
Reply to this answer
Add your answer
Answers are reviewed before publication.
_b84~torkiva.3:
SQLite also puts a lot of weight on backwards compatibility, so changing how existing declarations or values behave can be tricky.