Re:Postgres: Boundless `Text` and Back Again
by brandur on Friday September 10, 2021 @02:18PM
(#28485262)
With regards to the CHECK constraints, I actually used to recommend those because relaxing them seemed to be operationally safer, but re-reading the Postgres docs, it didn't seem like there was a particularly big advantage to them because Postgres is pretty smart changing types efficiently where possible. Is there another benefit I missed?
Interesting on the data warehouse note — we haven't actually implemented the domain idea yet (what's in the article is more musing about it), but it's good to know about this possible downside.
Re:Postgres: Boundless `Text` and Back Again
by mjw1007 on Friday September 10, 2021 @03:42PM
(#28486104)
Another advantage of using `varchar(n)` rather than a constraint is that the limit is visible in `information_schema.columns` and `pg_attribute`, so tools using this sort of introspection have a better chance of doing something useful.
(Incidentally, distinguishing between "text" and "varchar" the way you do in the article can be confusing, because in PostgreSQL plain `varchar` instead of `varchar(N)` is another way of getting an unconstrained character type.)
A word of caution when using domains: many SaaS offerings that replicate your Postgres database to a data warehouse do not support domains.