On 18 Jun 2021, at 9:34, David G. Johnston wrote: > On Thursday, June 17, 2021, Gavan Schneider <list.pg.gavan@xxxxxxxxxxx> > wrote: > >> >> My approach is to define such fields as ‘text’ and set a constraint using >> char_length(). This allows PG to do the business with the text in native >> form, and only imposes the cost of any length check when the field is >> updated… best of both worlds. >> > > Those are basically the same world…your alternative probably is strictly > worse than varchar(n) because of its novel way of implementing the same > functionality. > Not sure if this is strictly true. Novelty per se is not always worse. :) The design advantage is in all text fields being defined the same — no built in length. When it becomes apparent a length constraint is needed it can be added for the relevant field(s), e.g., when the system does not impose proper restraint at the input stage. > For most text fields any good constraint is going be done in the form of a > regular expression, one that at minimum prevents non-printable characters > (linefeed and carriage return being obvious examples). > Agree. If the business rules need some additional restrictions they can go here as well. Not so obvious that newlines, etc. are unwelcome. Consider the need for human readable text in comment fields (or other annotation where the readability is enhanced by such layout). There is always the consideration of SQL injection (but it’s mostly too late if we’re leaving this to a constraint) and other toxic string sequences. But this is all business logic. The database just needs to handle the ‘text’ and we need to design the restraint around the content. Gavan Schneider —— Gavan Schneider, Sodwalls, NSW, Australia Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat, plausible, and wrong. — H. L. Mencken, 1920
