Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)

> On Apr 29, 2020, at 12:34 AM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Tuesday, April 28, 2020, Rui DeSousa <rui(at)crazybean(dot)net <mailto:rui(at)crazybean(dot)net>> wrote:
>
> Arbitrarily? What’s a cusip, vin, ssn? Why would you put a btree index on a text field? Because it’s not.
>
> What you’re advocating is a NoSQL design — defer your schema design. Letting the application code littered in multiple places elsewhere define what a cusip, etc. is.
>
>
> All of those would be defined as PKs somewhere with a constraint that limits not only their length but also allowable characters so you don’t get something like !(at)#$%^&*( as a valid ssn of length 9. A domain is probably even better though has implementation trade-offs.
>
> A length constraint by itself is insufficient in those examples, which are still arbitrary though the decision is outside the control of the modeler. If the supplied values are external, which they likely are, the system under design should probably just define the values loosely and accept whatever the source system provides as-is.
>
> David J.

That is the worst; seeing a text field being used in a primary key; seriously? Trying to understand how wide a table is when it’s 40 columns wide and 35 of them are text fields, ugh. When someone asks for btree index on a column and it is a text field; why?

Don’t fool yourself, you are not future proofing your application; what really is happening is a slow creeping data quality issue which later needs a special project just clean up.

I think we can both agree that you need to model your data correctly or at least to your best knowledge and ability.