News for hackers. Stuff that matters.
Re:Making Database Systems Usable by ekimekim on Wednesday August 21, 2024 @12:48AM (#41306875)
This is changing. Postgres (as you noted) supports JSON, and it also has excellent native support for arrays, range types, and composite types. For example:

    CREATE TYPE number_with_unit (
        value DOUBLE PRECISION,
        unit TEXT
    );

    CREATE TABLE example (
        height number_with_unit, -- our composite type, eg. (6, 'ft') or (180, 'cm')
        known_aliases TEXT[], -- list of string
        active_times TSRANGE, -- time range, ie. (start, end) timestamp pair
    );

    SELECT height.unit,
           known_aliases[1] AS primary_alias,
           upper(active_times) - lower(active_times) AS time_active 
    FROM example;
Re:Making Database Systems Usable by globular-toast on Wednesday August 21, 2024 @05:49AM (#41308551)
You need to be aware of the tradeoffs, though. For example, adding an alias in your example could easily result in a lost update: if two users add an alias at the same time the second write will overwrite the first. You can go up an isolation level or check it in your application (do an `UPDATE ... WHERE` then check it went through), of course, but with relational tables you get this for free without any worry or performance impact.