Ecto - Postgresql wiki: Don't do this

By any does someone have a custom Ecto type or something to do that right out of the box?^^

I mean by setting an error like “too long entry” into the changeset instead of throwing the exception

ERROR 22001 (string_data_right_truncation) value too long for type character varying(255)

Not really necessary to be in sync, db constraint is “safety net” - works when everything else fails.
So, 100 symbols in validation and 255 in database is perfectly valid combo.

Right, that’s something I always think to myself when I develop a “for real” public site.

I’ll attach a maxlength to the input box, followed by a max (and potentially min) length with a changeset validation, followed by enforcing a text + constraint at the database level and finish it up with a test to ensure the changeset validates the length. Without all 4 (first one is mainly a UI hint) I feel really uneasy, but at the same time, it’s quite a bit of duplication and remembering to keep things in sync if one of those lengths change later.

Don’t quote me, but as I recall it sets the new constraint and wipes the old one atomically (and linearly, I.E. it can take time), and depending on set option it will either truncate or error at that time if anything is too long then.

I’ve always done the text + constraint manually which works well enough but I’ll admit, it’s a tiny bit inconvenient to always add it to your migration.

If you use varchar(N), what happens if you change N down the line? Does pg know to modify the underlying constraint’s length behind the scenes?

Depends on context. If you’re writing a public-facing application, yes, absolutely. Private in-house apps are a whole different issue…

If you use “text” everywhere and don’t always remember to set limits in validations expect someone to have 1GB username

Which will be pretty irritating for the end user as they will not know what is wrong. Which can be in any place from mildly irritating to that cannot ever happen and we resign from your services depending on what your application does.

Rarely, and even when I do then I do not treat it as “this limit will never change” as something like that is highly improbable. Twitter changed character limit, Git will change character limit, etc. Setting hard limit because right now you think that it will be set in stone isn’t wise. So application-side check is all you need, you do not need to impose limit on the DB as well.

Yes, that is big. But if you want to save 342 copies of whole War and Peace in one text field then I would expect bigger problems before it would even hit the DB.

Then you never validate your string length? If you do, this argument doesn’t hold. If you don’t, then to each his own I guess.

Would only happen with some extremely uncommon combination of new unicode code points, and the only risk is having a DB error instead of the changeset validation error, so it wouldn’t even be irritating for anyone (a user playing around with the weirdest of strings would not complain).
If you really want to take into account such kind of unlikely scenario, then you should always define a max length as there is a risk your string will be too long to store (“there is always a bigger fish”) as even text fields always have a hard coded max size (1GB for Postgres ; that’s a big fish).

As I said earlier, you almost never know the size of the text beforehand. “There is always a bigger fish” as it was said and almost any character limit will be lifted sooner or later.

Also as I said - problem with characters counting. For example imagine that your DB doesn’t adapt new Unicode standard or it was not yet updated to use newer tables. Now you can get counting mismatches between your application and DB which will lead to irritating errors