From: Rui DeSousa <rui@crazybean.net>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: raf <raf@raf.org>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
Date: Wed, 29 Apr 2020 00:57:29 -0400
Message-ID: <A5AAA4FD-DC34-472E-8F9A-AD8ABAF203F1@crazybean.net> (raw)
In-Reply-To: <CAKFQuwbH0sB8Rc0LMV77fUkP9c-foqEfzCCDLSusTfaG933Jvw@mail.gmail.com>
References: <CAOasRJYCUwMYdd=BL29TupW_iCbvM3YRM01YTB_WYSUEays3CA@mail.gmail.com>
<CAKhGwmB_C4a1XVrzYsr3CFmUxegZyvsC=7QtzkW6TP27J5G31w@mail.gmail.com>
<20200428234332.7nkyomr2unlez6p5@raf.org>
<8EC9D082-B541-435F-9EE3-3ED14AA3C2D1@crazybean.net>
<20200429022928.fywdmiuc6oqpmgtc@raf.org>
<D9F0D7D1-9D46-4B9B-8B62-EE139159F62E@crazybean.net>
<CAKFQuwbH0sB8Rc0LMV77fUkP9c-foqEfzCCDLSusTfaG933Jvw@mail.gmail.com>
> On Apr 29, 2020, at 12:34 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> On Tuesday, April 28, 2020, Rui DeSousa <rui@crazybean.net <mailto:rui@crazybean.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 !@#$%^&*( 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: rui@crazybean.net, david.g.johnston@gmail.com, raf@raf.org, pgsql-admin@lists.postgresql.org
Subject: Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
In-Reply-To: <A5AAA4FD-DC34-472E-8F9A-AD8ABAF203F1@crazybean.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox