If you're at all likely to be defining views that contain any of these columns, I would strongly advocate using text with the check constraint, rather than varchar. The reason being: if you ever need to increase the column's maximum length, in the case of varchar you will be prohibited from making this change because it violates the data type dependency between the table and the view.<p>To cope with this, in the same transaction that you're increasing the length of the varchar, you will also need to drop and recreate all such dependent views (and any other views that depend upon <i>those</i> views, etc).<p>This is not fun.<p>In comparison: increase the maximum length of the check constraint, job done.
The way I like to solve this is to still define the columns as `TEXT`, but just add a custom <i>named</i> CHECK constraint that validates the maximum length.<p>Eg: `ALTER TABLE users ADD CONSTRAINT username_max_len CHECK LENGTH(username) <= 100 NOT VALID;`<p>The NOT_VALID option skips checking the existing data, so the migration is quasi-instant.<p>That way the constraint can easily be swapped out by removing the old one and adding a new one, and you have maximum flexibility going forward.<p>This also doesn't require an index rebuild, and better models what's actually happening internally.
We had similar experiences, and now use varchar 99, varchar 999, varchar 9999. The purpose of the "9" digits is to highlight that it's our plausible guesstimate for "good enough" and can be increased later, rather than any definite unchangeable limit.
When thinking about length limits, always try to come up with use cases for the data that limit the length.<p>If you have a legal name, you might want to write that person a letter (an invoice, injunction, notification of breach, whatever), so it must fit into the address field. If you allow a 2000 character name, it likely <i>won't</i> fit. Same for street names and the likes.<p>Not fitting a full name into a field sucks, but at least it gives the one filling it the choice how to shorten it.<p>If the data goes into some kind of webpage, does it even render properly if it's very large? If it goes into a hover text, or in a box that's too small for a proper scroll bar, it becomes unreadable it too big.<p>If text becomes <i>really</i> big (and it might be gigabytes if there's no limit), storage cost becomes an issue.<p>From this perspective, you basically never want unlimited TEXT fields anywhere, at least not exposed to customers. Maybe it's OK for internal some internal, at least if you can yell at your users if they abuse it too much.
I recently discovered the joy of PostgreSQL via Supabase.io (which is very awesome too, anyone else using it?)<p>The text thing is awesome but I never thought I could be so excited about Policies. It makes everything feel so much more relaxing knowing you can’t accidentally give someone access to something they shouldn’t be able to edit.
I am on this journey as well. We are using constraints that check the length of a text field instead of using varchar. I like the idea of setting a few standard lengths though.<p>A word of caution when using domains: many SaaS offerings that replicate your Postgres database to a data warehouse do not support domains.
>varchar(200) for shorter-length strings like names, addresses, email addresses, etc...The idea is to pick liberal numbers that are easily long enough<p>I'm not convinced 200 is liberal enough for some of that.<p>For email, a 64 character local part is RFC compliant, then 1 for the '@', and the domain can be 253 characters.<p>Probably similar edge cases for addresses.
I agree with the “order of magnitude” text size limit. I tend to put 10x of what I would consider the expected average. A bit like cryptographers put 2x the number of rounds they think may be eventually broken.<p>Why should the database be the input sanitizer, though? Intermediary systems can break or degrade before it reaches PostgreSQL.
I thought of the DOMAIN solution as I was reading the article (although I didn't know DOMAIN was the right mechanism), but the naming convention I had in mind mimicked Rust/stdint and put the character limit in the name—something like varchar200 or char200.
I believe that Postgres, following the SQL standard, silently truncates longer strings when using varchar(n), which is probably not a desirable result if you're using it for validation.