3 ms·
There is nothing called "VARCHAR(n)" in SQLite. It is mapped to `CHAR(ACTER) => TEXT` which is limited to 2^31 - 1 characters. [1] https://www.sqlite.org/datat
by NicolaiS 4y ago
There is nothing called "VARCHAR(n)" in SQLite. It is mapped to `CHAR(ACTER) => TEXT` which is limited to 2^31 - 1 characters.
[1] https://www.sqlite.org/datatype3.html#3.1.1 https://www.sqlite.org/datatype3.html#3.1.1
[2] https://www.sqlite.org/limits.html#max_length https://www.sqlite.org/limits.html#max_length
- dekhn 4y agoThats user hostile behavior- VARCHAR is a critical optimization for large databases.
- masklinn 4y agoWhat part of varchar is a “critical optimisation” exactly? If it’s ensuring that the data is never larger than X, then you can use a CHECK constraint. It has no impact on storage in sqlite, or in postgres for that matter.
- Ndymium 4y agoIn PostgreSQL a varchar is also a text type column but with an added length check. So it's actually just slower to insert and not more space efficient, as far as I know. So SQLite is not alone in this.
- chasil 4y agoThese are the native SQLite datatypes, that are also used as column affinities. TEXT - text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE). BLOB - blob of data, stored exactly as it was input. NUMERIC - generic number, attempts to devolve to integer or real. INTEGER - signed, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. REAL - floating point value, stored as an 8-byte IEEE-754 format. Any CHAR variant is really text. If you really need it to be 50 characters, then a suitable CHECK constraint must be in place. You can also create tables with unknown types: CREATE TABLE foo(bar razzamataz); With some gymnastics, you can see the affinity assigned to this column: create table bar as select * from foo where 1=0; sqlite> .dump bar PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE bar( bar NUM ); COMMIT;