Re: ALTER TEXT field to VARCHAR(1024)

On Mon, Sep 22, 2014 at 10:40 AM, John McKown
<john(dot)archie(dot)mckown(at)gmail(dot)com> wrote:
> On Mon, Sep 22, 2014 at 10:31 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> I'll pile on here: in almost 20 years of professional database
>> development I've never had an actual problem that was solved by
>> introducing or shortening a length constraint to text columns except
>> in cases where overlong strings violate the data model (like a two
>> character state code for example). It's a database equivalent of "C
>> programmer's disease". Input checks from untrusted actors should
>> happen in the application.
>>
>> merlin
>>
>
> I do not have your experience level with data bases, but if I may, I
> will make an addition. Input checks should also happen in the RDBMS
> server. I have learned you cannot trust end users _or_ programmers.
> Most are good and conscientious. But there are a few who just aren't.
> And those few seem to be very prolific in making _subtle_ errors. Had
> one person who was really good at replacing every p with a [ and P
> with {

Sure. The point is distinguishing things which are *demonstrably*
false (like a US VIN must be exactly 17 chars) from those that are
based assumption (such as a cityname must be <= 50 characters). The
former should be validated in the schema and the latter should not be.
If you're paranoid about the user submitting 100mb strings for
"username" and don't trust the application to deal with that, I'd
maybe consider making a domain 'safetext' which checks length on the
order of a few thousand bytes and using that instead of 'text' and use
it everywhere. This will prevent the dba from outsmarting the
datamodel which is a *much* bigger problem in practice than the one
length checks attempt to solve.

Domains have certain disadvantages (like no array type) -- be advised.

merlin