SQLite or PostgreSQL? It's Complicated

Re:SQLite or PostgreSQL? It's Complicated by msbarnett on Tuesday June 28, 2022 @12:39PM (#31909965)

Sorry, "corruption" was probably misleading there. By "silent data inconsistency/corruption" I mean, "a bug in my application led to me storing strings in an integer column, or too-long data in a too-short column, and SQLite just silently let me do that"-type errors – the data consistency is logically (but not physically) corrupted. The author encountered 2 separate occurrences of this just in their simple use-case, evidently.

[ Parent | on HN ]

Re:SQLite or PostgreSQL? It's Complicated by 323 on Tuesday June 28, 2022 @12:53PM (#31910128)

> led to me storing strings in an integer column

Recent SQLite versions have STRICT mode where they forbid that.

https://www.sqlite.org/stricttables.html

> or too-long data in a too-short column

You can use a constraint for that:

    CREATE TABLE test ( 
        name TEXT NOT NULL CHECK(length(name) <= 20)
    ) STRICT;

[ Parent | on HN ]

Re:SQLite or PostgreSQL? It's Complicated by mikepurvis on Tuesday June 28, 2022 @06:12PM (#31913753)

Neat, will definitely give this a try. Definitely one of my minor peeves about SQLite is having to set a bunch of pragmas on every new connection. Like, especially stuff such as foreign_keys=1... really, enforcement of foreign key constraints is something each connection has to opt into rather than being set one on the whole DB file and never thought about again? Blah.

[ Parent | on HN ]