4 ms·
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:
by ekimekim 2y ago
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;
- globular-toast 2y agoYou 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.