<story><title>Type constraints in 65 lines of SQL</title><url>https://supabase.com/blog/type-constraints-in-65-lines-of-sql</url></story><parent_chain></parent_chain><comment><author>sa46</author><text>Nice article. I&amp;#x27;ve used composite types with success for similar problems. Some tips:&lt;p&gt;- Composite types are useful for data validation. Money types and unit-based types are good for composite types.&lt;p&gt;- 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.&lt;p&gt;- Like the article notes, avoid using composite types for data types that may change.&lt;p&gt;- 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.&lt;p&gt;- Using composite types is mildly annoying since you must wrap the composite column in parenthesis to access the field.&lt;p&gt;&lt;pre&gt;&lt;code&gt;    -- Bad 
    SELECT package_semver.major FROM package_version;
    -- Errors with `missing FROM-clause entry for table &amp;quot;package_semver&amp;quot;`
    
    -- Good
    SELECT (package_semver).major FROM package_version;
&lt;/code&gt;&lt;/pre&gt;
- 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 &amp;quot;it failed.&amp;quot;&lt;p&gt;&lt;pre&gt;&lt;code&gt;    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), &amp;#x27;&amp;#x27;) = &amp;#x27;object&amp;#x27;);
    
    ALTER DOMAIN item_delta_node
      ADD CONSTRAINT item_delta_node_item_delta_id_null_or_num
        CHECK (coalesce(jsonb_typeof(value[&amp;#x27;item_delta_id&amp;#x27;]), &amp;#x27;number&amp;#x27;) IN (&amp;#x27;null&amp;#x27;, &amp;#x27;number&amp;#x27;));&lt;/code&gt;&lt;/pre&gt;</text></comment>
<story><title>Type constraints in 65 lines of SQL</title><url>https://supabase.com/blog/type-constraints-in-65-lines-of-sql</url></story><parent_chain></parent_chain><comment><author>enugu</author><text>Any idea of how to implement tagged unions in Postgres. For example, Tree a = Leaf a | Branch (Tree a, Tree a)&lt;p&gt;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?&lt;p&gt;Dont need polymorphism(say a = String). Even a non recursive tagged union would be helpful.</text></comment>