Ecto - Postgresql wiki: Don't do this

That is why I use :text almost always when want to store string in the DB.

This is the way to go when using Postgres, there is no advantage in using varchar for shorter text. But with Ecto lingo (string is for strings, right?) and switching between the databases / ecosystems I still don’t do it, at least not always

There is no advantage and there is one. Personally I am in the “text for everything” camp, but I saw people arguing that varchar with limit prevents to some extent users from abusing your web site with huge chunks of text. Something that could in theory happen if you missed your validations.

I have never seen that being a problem to be fair in my entire career but I kind of get the point they are making.

If you validate the data against a max length anyway, then what’s the point of using text instead of varchar(max length)? I personally use text only when I cannot decide over a max length, and in such cases there is indeed the risk of people inserting looong text in there.

If you check in 2 places, you have to keep them in sync when, invariably, a limit turns out to be too low. If you check in your validations, you can typically provide better feedback to users.

My 2 cents:
Those Postres guys who’ve put together these recommendations are RDBMS titans. Change is hard and I always expect resistance, but following other’s experience is always the right road to progress.
I personally think at the end of the day, we should try to go that way regardless of minor problems.

Another problem is syncing the way both implementations count characters. Are you sure that your DB counts them in the same way as your application? Additionally in most cases I encountered the string has undefined length, so handling these few cases when there are limits differently isn’t worth the effort and possible bugs.

Keeping them in sync is no more difficult than setting the size as a constant in the schema (plus macro for access from outside) and using it both in the validation function and the migration. Validation is indeed necessary for better feedback, but as it doesn’t make things more difficult to maintain I don’t think it’s a good habit to not limit the size in the DB as well.

Honestly I would bin the one which doesn’t follow the spec for the selected character set (usually UTF-8 for me).
I can see the charm of using only text as we never have to pay attention, but if you regret the decision later (e.g. for performance reason) it’s difficult to define a size afterwards (on the contrary, growing the size of a string is bug-proof and thus almost painless).

What do you do when a DBA edits a text field with a sentence longer than what your validations agree with? Or if another app using the same DB does the same because it didn’t implement the same validations? You can end up with many bugs that could have been avoided with stronger in-DB validations.

Doesn’t PostgreSQL just do a text + constraint when a varchar(N) is used?

No such reason in case of Postgres, text and varchar are basically the same under the hood, see this article for more info: CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT – UPDATED 2010-03-03 – select * from depesz;