6 ms·

>Jerod Santo: ...if I want this to be a varchar 40 and you let me put anything in there, then why did I declare it to be a varchar 40 in the first place? You kn

>Jerod Santo: ...if I want this to be a varchar 40 and you let me put anything in there, then why did I declare it to be a varchar 40 in the first place? You know what I’m saying? >Richard Hipp: Yeah, exactly. If you say it’s a varchar 40 and you an integer there, it will change it into text. Or if you have a comment that’s declared integer and you try to put text in it, it looks like an integer and it can be converted without loss. It will convert and store it as an integer. But if you try and put a blob into a short int or something, there’s no way to convert that, so it just stores the original and it gives flexibility there. Am I the only one annoyed by this? Having a var datatype that acts this way is useful, but not being able to trust other datatypes to enforce type constraints is not ideal. Or if you don't actually have a varchar 40 type, and you just have something that's text-like without other more specific constraints, then call it 'text'. Or allow strongly typed types along with var-type types. Or have a config to choose how types behave. Like, mssql differs from ANSI for NULL handling, but you can specify USE ANSI NULLS.

Scaevolus 4y ago
You can opt into this now with STRICT tables: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
debugnik 4y ago
> not being able to trust other datatypes to enforce type constraints is not ideal You can have more reasonable behaviour now by creating tables as "strict"[1], in which SQLite will fail if it can't perform a valid conversion. For storing values of arbitrary type it makes available a new "any" type instead. > if you don't actually have a varchar 40 type, and you just have something that's text-like without other more specific constraints, then call it 'text' It is in fact called text, it just supports aliases for common ways of specifying text fields in other DBs. However, "strict" tables to the rescue again; they also disable type names other than the native ones. [1]: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
deleted 4y ago
[deleted]
giraffe_lady 4y ago
Like a lot of people, and especially coming from postgres, I recoiled from it at first. In practice it has never caused me a significant issue that I can recall and saves a lot of time casting things into compatible columns for exploratory queries. I'm still not sure I like it. But shifting into sqlite mindset is important for making the most of sqlite's strengths, and learning to think of these as "affinities" rather than types or constraints has been part of that.
kevin_thibedeau 4y ago
SQLite is a Tcl library. All of its type flexibility is meant to work with limited friction in that ecosystem. In Tcl every object type has a binary and string representation and can be freely converted.
- blacksqr 4y ago
  Also, I believe one of SQLite's original design goals was Postgres compatibility. So a lot of the games SQLite plays with type notations and coercions are intended to make it possible for SQL code written for Postgres to run with SQLite, to the greatest extent possible given Sqlite's underlying architecture.

Scaevolus 4y ago

You can opt into this now with STRICT tables: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html

debugnik 4y ago

> not being able to trust other datatypes to enforce type constraints is not ideal You can have more reasonable behaviour now by creating tables as "strict"[1], in which SQLite will fail if it can't perform a valid conversion. For storing values of arbitrary type it makes available a new "any" type instead. > if you don't actually have a varchar 40 type, and you just have something that's text-like without other more specific constraints, then call it 'text' It is in fact called text, it just supports aliases for common ways of specifying text fields in other DBs. However, "strict" tables to the rescue again; they also disable type names other than the native ones. [1]: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html

deleted 4y ago

[deleted]

giraffe_lady 4y ago

Like a lot of people, and especially coming from postgres, I recoiled from it at first. In practice it has never caused me a significant issue that I can recall and saves a lot of time casting things into compatible columns for exploratory queries. I'm still not sure I like it. But shifting into sqlite mindset is important for making the most of sqlite's strengths, and learning to think of these as "affinities" rather than types or constraints has been part of that.

kevin_thibedeau 4y ago

SQLite is a Tcl library. All of its type flexibility is meant to work with limited friction in that ecosystem. In Tcl every object type has a binary and string representation and can be freely converted.

blacksqr 4y ago
Also, I believe one of SQLite's original design goals was Postgres compatibility. So a lot of the games SQLite plays with type notations and coercions are intended to make it possible for SQL code written for Postgres to run with SQLite, to the greatest extent possible given Sqlite's underlying architecture.