Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jTen0-0001xp-6u for pgsql-admin@arkaria.postgresql.org; Wed, 29 Apr 2020 04:57:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jTemx-0007Gn-7S for pgsql-admin@arkaria.postgresql.org; Wed, 29 Apr 2020 04:57:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jTemw-0007Gd-MV for pgsql-admin@lists.postgresql.org; Wed, 29 Apr 2020 04:57:38 +0000 Received: from impout007aa.msg.chrl.nc.charter.net ([47.43.20.31] helo=impout007.msg.chrl.nc.charter.net) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jTemq-00018D-A0 for pgsql-admin@lists.postgresql.org; Wed, 29 Apr 2020 04:57:37 +0000 Received: from cerberus.crazybean.net ([24.151.69.73]) by cmsmtp with ESMTP id TemojzQALRzWKTempjXC1H; Wed, 29 Apr 2020 04:57:31 +0000 Authentication-Results: crazybean.net; none X-Authority-Analysis: v=2.3 cv=estrMbhX c=1 sm=1 tr=0 a=YXTuGtgWN9YX+aIhe8tYuA==:117 a=YXTuGtgWN9YX+aIhe8tYuA==:17 a=xqWC_Br6kY4A:10 a=cl8xLZFz6L8A:10 a=pGLkceISAAAA:8 a=oIfrFNJ-AAAA:8 a=2_XL7sJR4rdNwiS5VVAA:9 a=Gg8A7WGsiytUzWDb:21 a=1nP-K453ODvYSikT:21 a=QEXdDO2ut3YA:10 a=1pbA8seeoiLujkW11SkA:9 a=KuVLRYvfxpbulvMf:21 a=zim1lpkprX-Uqs25:21 a=ZRIXypUrYb5fXO-5:21 a=_W_S_7VecoQA:10 a=Cg2NcamS17xU0rR7el6q:22 Received: from [IPv6:2001:470:e117:4:45f1:366f:a08a:8c7] ( [2001:470:e117:4:45f1:366f:a08a:8c7]) by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id a00ef7a2 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Wed, 29 Apr 2020 00:57:30 -0400 (EDT) From: Rui DeSousa Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_05E87FBF-7CB3-4F1E-BD11-15CA3D015021" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.80.23.2.2\)) Subject: Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Date: Wed, 29 Apr 2020 00:57:29 -0400 In-Reply-To: Cc: raf , "pgsql-admin@lists.postgresql.org" To: "David G. Johnston" References: <20200428234332.7nkyomr2unlez6p5@raf.org> <8EC9D082-B541-435F-9EE3-3ED14AA3C2D1@crazybean.net> <20200429022928.fywdmiuc6oqpmgtc@raf.org>

X-Mailer: Apple Mail (2.3608.80.23.2.2) X-CMAE-Envelope: MS4wfOYxpJelBb0cctWj+KcuN7+7ldzAOtQq4w0xidmIliQRlZDOZ3YZdKgK0vBlft5MGleF1E2q8DiofajinoZ3PmAPZA5fwv7gwi451TGcPCPNL1mcLtik 48W8YRo+aIh9X86HZcER9HZOxKUz/iUmK6A6t/lBqvM89yLG9/me8IzGL5w5Ffaoe5C9woHLg4xwjQA8Kt9X1F2i0ceBcXu9Vz2WTg8ef7SEVBAHPyIdOq8T Rnce5vKJSwhEhWllPx5Ldw== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_05E87FBF-7CB3-4F1E-BD11-15CA3D015021 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Apr 29, 2020, at 12:34 AM, David G. Johnston = wrote: >=20 > On Tuesday, April 28, 2020, Rui DeSousa > wrote: >=20 > Arbitrarily? What=E2=80=99s a cusip, vin, ssn? Why would you put a = btree index on a text field? Because it=E2=80=99s not. >=20 > What you=E2=80=99re advocating is a NoSQL design =E2=80=94 defer your = schema design. Letting the application code littered in multiple places = elsewhere define what a cusip, etc. is.=20 >=20 >=20 > 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=E2=80=99t get something like !@#$%^&*( as a valid ssn of length 9. = A domain is probably even better though has implementation trade-offs. >=20 > 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. >=20 > 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=E2=80=99s = 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=E2=80=99t 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. --Apple-Mail=_05E87FBF-7CB3-4F1E-BD11-15CA3D015021 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

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> wrote:
Arbitrarily? What=E2=80=99s a cusip, = vin, ssn? Why would you put a btree index on a text = field? Because it=E2=80=99s not.

What = you=E2=80=99re advocating is a = NoSQL design =E2=80=94 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=E2=80=99t 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=E2=80=99s 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=E2=80=99t 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.

= --Apple-Mail=_05E87FBF-7CB3-4F1E-BD11-15CA3D015021--