Type Constraints in 65 lines of SQL

Re:Type Constraints in 65 lines of SQL by oliverrice on Friday February 17, 2023 @01:19PM (#34838302)

One of the restrictions of composite types is that they can not contain an instance of themselves. So unfortunately, this is not currently possible.

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

[3] https://github.com/supabase/pg_jsonschema

[ Parent | on HN ]

Re:Type Constraints in 65 lines of SQL by nextaccountic on Friday February 17, 2023 @02:28PM (#34839158)

What's your opinion between jsonb vs hstore columns? https://www.postgresql.org/docs/15/hstore.html

[ Parent | on HN ]

Re:Type Constraints in 65 lines of SQL by oliverrice on Friday February 17, 2023 @07:06PM (#34842353)

IIRC, hstore pre-dated jsonb. I'm not aware of any performance differences for the object type but it would be worth checking if you have a heavy workload. jsonb will tend to have better ORM support (if thats something you care about) and it also isn't behind an extension which is a (small) perk

[ Parent | on HN ]

Re:Type Constraints in 65 lines of SQL by nextaccountic on Friday February 17, 2023 @02:33PM (#34839260)

Actually, another thing. JSON works. But what about doing pattern matching on the tagged enum? Each variant should give access to its fields

Can https://www.postgresql.org/docs/15/functions-conditional.htm... be used to do it?

[ Parent | on HN ]