from Hacker News

Type constraints in 65 lines of SQL

by oliverrice on 2/17/23, 2:31 PM with 46 comments

• by sa46 on 2/17/23, 11:02 PM

  Nice article. I've used composite types with success for similar problems. Some tips:

  - 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;
  

  - When defining a domain type, separate checks into named constraints with ALTER DOMAIN. The Postgres error message for check validation failures is lackluster and provides little beyond "it failed."

      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'));

• by enugu on 2/17/23, 6:13 PM

  Any idea of how to implement tagged unions in Postgres. For example, Tree a = Leaf a | Branch (Tree a, Tree a)

  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.

• by nickpeterson on 2/17/23, 5:13 PM

  I often wonder about this kind of solution compared with a more traditional solution of using a table of columns and constraints. There seems like there should be an obvious heuristic of when to use one or the other but it eludes me.

• by oliverrice on 2/17/23, 3:11 PM

  hi, author here happy to answer any questions

  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

• by margorczynski on 2/17/23, 11:44 PM

  The benefits of having a better type and form constraint is obvious. But you should remember that offloading computation to the DB, especially one that isn't fully distributed like Postgres. Might bite you later down the line.

• by maxloh on 2/18/23, 10:30 AM

  I once heard that data validation should be implemented in back end server but not datsbase because the latter is more difficult to scale horizontally and usually the bottleneck of transaction performance.

  Is that true?