I am doing a talk at Postgres Conf about Postgres’s type system. I already asked about this and got some great responses:
https://www.postgresql.org/message-id/flat/b82f8886db61a3395e6eab94981258274cdbacd3.camel%40cybertec.at#f13288b72bd2d564ee758c1a273652ad
Those responses discussed mostly gotchas with built-in types. Lots of good stuff, thanks, all!
But what *really* sets Postgres apart from comparable systems is user defined types. I would like to carefully lay out how to define and use a user-defined type (I don’t think I have time to dig into doing fancy stuff with C functions, so just the basic “user defined sum type”), but also any gotchas.
And I’d like to finish with some thoughts about when and how to use user-defined types. My feeling is that this feature is greatly under-used, mostly because it’s so non-standard. But AFAICT, user-defined types are fine and other than some ugliness due to SQL (mainly needing parentheses in some unexpected places), fields in a user defined type work perfectly well in Postgres’s SQL. I guess you’d need to pull them apart for values returned to clients, but that isn’t difficult.
So, any gotchas with user defined types? Any thoughts about designing with them?
https://www.postgresql.org/message-id/flat/b82f8886db61a3395e6eab94981258274cdbacd3.camel%40cybertec.at#f13288b72bd2d564ee758c1a273652ad
Those responses discussed mostly gotchas with built-in types. Lots of good stuff, thanks, all!
But what *really* sets Postgres apart from comparable systems is user defined types. I would like to carefully lay out how to define and use a user-defined type (I don’t think I have time to dig into doing fancy stuff with C functions, so just the basic “user defined sum type”), but also any gotchas.
And I’d like to finish with some thoughts about when and how to use user-defined types. My feeling is that this feature is greatly under-used, mostly because it’s so non-standard. But AFAICT, user-defined types are fine and other than some ugliness due to SQL (mainly needing parentheses in some unexpected places), fields in a user defined type work perfectly well in Postgres’s SQL. I guess you’d need to pull them apart for values returned to clients, but that isn’t difficult.
So, any gotchas with user defined types? Any thoughts about designing with them?
