another approach that works great is to use `create type`[1] with an `input_function` and `output_function` to build on top of an existing scalar type. For example, using that method would allow semver to be persisted as a string natively. The only downside to that is you have to be superuser.
[1] https://www.postgresql.org/docs/current/sql-createtype.html
But there are far fewer tutorials and best practices on how to, say, maintain a library of Postgres functions, types, and stored procedures that can be iterated on. I'd venture that most people have no idea how powerful their databases can be.
1) Its only been a few years since the trend of testing on sqlite locally and running postgres in production went away in favor of postgres everywhere (probably thanks to docker). That prevented using any feature that wasn't equally supported by both.
2) There's definitely a knowledge gap, and not just among developers. The features are most useful for building rich applications, so even DBAs didn't have much incentive to use them prior to tools like supabase using the database as the data model source-of-truth.
3) Companies are increasingly deciding that data is their competitive advantage and interest in data integrity is growing. Database constraints are unparalleled at that because they can't be sidestepped
Is that true?
This is one data type feature which would be great to have. I know you can create separate tables for each option in the type and use an id, but is a direct type implementation possible?
Dont need polymorphism(say a = String). Even a non recursive tagged union would be helpful.
I have saved some links about this
https://github.com/solidsnack/pg-sql-variants
https://thelyfsoshort.io/variant-types-in-postgresql-c63725f...
https://www.parsonsmatt.org/2019/03/19/sum_types_in_sql.html
https://typeable.io/blog/2019-11-21-sql-sum-types.html
Right now I think the ideal is a jsonb column (with a field that stores the tag) plus https://github.com/supabase/pg_jsonschema. But this is only usable if your language translates to/from JSON and generates json schema for you.
Rust can do this with https://serde.rs/ and https://docs.rs/schemars/latest/schemars/
I had this issue when trying to implement an AST type for pg_graphql[1] back when it was written in SQL [2]. In the end we used a JSON type which was much less constrained. That might be solvable using pg_jsonschema [3] if you really wanted to have a good time though
[1] https://github.com/supabase/pg_graphql
[2] https://github.com/supabase/pg_graphql/blob/34cc266da972d356...
Can https://www.postgresql.org/docs/15/functions-conditional.htm... be used to do it?
In most cases, a single invoice, order, deal, etc. is unlikely to be generated using multiple currencies. Therefore, a single currency_type field in the invoice table would be sufficient.
If we used a composite currency type, the currency_type field would be highly redundant.
It can help prevent the bug in which someone adds up multiple currencies without conversion.
“25 USD + 25 EUR” will either fail or work via some currency conversion routine.
“25 + 25” will produce a meaningless and useless wrong answer.
For example, SemVer requires that two version that differ only in metadata e.g. `1.0.0+metaA` and `1.0.0+metaB` should be considered equal. It would be error prone and tedious to push that logic onto every query that wanted to sort the table, but with a type we can define the logic once and have it work everywhere.
- Composite types are useful for data validation. Money types and unit-based types are good for composite types.
- Avoid over-using composite types. Most of the time, regular columns are better. Not many tools interact well with composite types, like reporting tools or database libraries.
- Like the article notes, avoid using composite types for data types that may change.
- A JSONB domain type is a good alternative for data types that change often. Note that if you put the validation into a helper function, Postgres will not revalidate the domain if the function definition changes.
- Using composite types is mildly annoying since you must wrap the composite column in parenthesis to access the field.
-- Bad
SELECT package_semver.major FROM package_version;
-- Errors with `missing FROM-clause entry for table "package_semver"`
-- Good
SELECT (package_semver).major FROM package_version;
CREATE DOMAIN item_delta_node AS jsonb NOT NULL;
ALTER DOMAIN item_delta_node
ADD CONSTRAINT item_delta_node_is_object
CHECK (coalesce(jsonb_typeof(value), '') = 'object');
ALTER DOMAIN item_delta_node
ADD CONSTRAINT item_delta_node_item_delta_id_null_or_num
CHECK (coalesce(jsonb_typeof(value['item_delta_id']), 'number') IN ('null', 'number'));""Using composite types is mildly annoying since you must wrap the composite column in parenthesis to access the field"""
^ yeah that is frustrating
--
if you create domains over jsonb frequently, check out pg_jsonschema[1] as a way to express the constraints more concisely
Is that true?