pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
22+ messages / 9 participants
[nested] [flat]
* PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
@ 2020-04-28 09:22 Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 09:43 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Holger Jakobs <holger@jakobs.com>
2020-04-28 11:46 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
2020-04-28 16:24 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Paul Carlucci <paul.carlucci@gmail.com>
0 siblings, 3 replies; 22+ messages in thread
From: Rajin Raj @ 2020-04-28 09:22 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; pgsql-hackers@postgresql.org
Is there any impact of using the character varying without providing the
length while creating tables?
I have created two tables and inserted 1M records. But I don't see any
difference in pg_class. (size, relpage)
create table test_1(name varchar);
create table test_2(name varchar(50));
insert into test_1 ... 10M records
insert into test_2 ... 10M records
vacuum (full,analyze) db_size_test_1;
vacuum (full,analyze) db_size_test_2;
Which option is recommended?
*Regards,*
*Rajin *
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
@ 2020-04-28 09:43 ` Holger Jakobs <holger@jakobs.com>
2 siblings, 0 replies; 22+ messages in thread
From: Holger Jakobs @ 2020-04-28 09:43 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
No, there is no impact.
Am 28.04.20 um 11:22 schrieb Rajin Raj:
> Is there any impact of using the character varying without providing
> the length while creating tables?
> I have created two tables and inserted 1M records. But I don't see any
> difference in pg_class. (size, relpage)
>
> create table test_1(name varchar);
> create table test_2(name varchar(50));
>
> insert into test_1 ... 10M records
> insert into test_2 ... 10M records
>
> vacuum (full,analyze) db_size_test_1;
> vacuum (full,analyze) db_size_test_2;
>
> Which option is recommended?
>
> *Regards,*
> *Rajin *
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
@ 2020-04-28 11:46 ` Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
2020-04-28 15:10 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Holger Jakobs <holger@jakobs.com>
2 siblings, 1 reply; 22+ messages in thread
From: Ashutosh Bapat @ 2020-04-28 11:46 UTC (permalink / raw)
To: Rajin Raj <rajin.raj@opsveda.com>; +Cc: pgsql-admin@lists.postgresql.org, pgsql-hackers <pgsql-hackers@postgresql.org>
On Tue, Apr 28, 2020 at 2:53 PM Rajin Raj <rajin.raj@opsveda.com> wrote:
>
> Is there any impact of using the character varying without providing the length while creating tables?
> I have created two tables and inserted 1M records. But I don't see any difference in pg_class. (size, relpage)
>
> create table test_1(name varchar);
> create table test_2(name varchar(50));
I don't think there's a difference in the way these two are stored
on-disk. But if you know that your strings will be at most 50
characters long, better set that limit so that server takes
appropriate action (i.e. truncates the strings to 50).
--
Best Wishes,
Ashutosh Bapat
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 11:46 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
@ 2020-04-28 15:10 ` Holger Jakobs <holger@jakobs.com>
2020-04-28 16:39 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Holger Jakobs @ 2020-04-28 15:10 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Truncation will NEVER happen. PostgreSQL throws an ERROR on any attempt
of saving more characters (not bytes!) into a VARCHAR(50) column.
There is some other well-known system which silently truncates, but we
all know why we would never use that.
Am 28.04.20 um 13:46 schrieb Ashutosh Bapat:
> On Tue, Apr 28, 2020 at 2:53 PM Rajin Raj <rajin.raj@opsveda.com> wrote:
>> Is there any impact of using the character varying without providing the length while creating tables?
>> I have created two tables and inserted 1M records. But I don't see any difference in pg_class. (size, relpage)
>>
>> create table test_1(name varchar);
>> create table test_2(name varchar(50));
> I don't think there's a difference in the way these two are stored
> on-disk. But if you know that your strings will be at most 50
> characters long, better set that limit so that server takes
> appropriate action (i.e. truncates the strings to 50).
>
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 11:46 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
2020-04-28 15:10 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Holger Jakobs <holger@jakobs.com>
@ 2020-04-28 16:39 ` Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Ron @ 2020-04-28 16:39 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
An example:
test=# create table bargle (f1 varchar(10));
CREATE TABLE
test=# insert into bargle values ('01234567890123');
ERROR: value too long for type character varying(10)
On 4/28/20 10:10 AM, Holger Jakobs wrote:
> Truncation will NEVER happen. PostgreSQL throws an ERROR on any attempt of
> saving more characters (not bytes!) into a VARCHAR(50) column.
>
> There is some other well-known system which silently truncates, but we all
> know why we would never use that.
>
> Am 28.04.20 um 13:46 schrieb Ashutosh Bapat:
>> On Tue, Apr 28, 2020 at 2:53 PM Rajin Raj <rajin.raj@opsveda.com> wrote:
>>> Is there any impact of using the character varying without providing the
>>> length while creating tables?
>>> I have created two tables and inserted 1M records. But I don't see any
>>> difference in pg_class. (size, relpage)
>>>
>>> create table test_1(name varchar);
>>> create table test_2(name varchar(50));
>> I don't think there's a difference in the way these two are stored
>> on-disk. But if you know that your strings will be at most 50
>> characters long, better set that limit so that server takes
>> appropriate action (i.e. truncates the strings to 50).
>>
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
@ 2020-04-28 16:24 ` Paul Carlucci <paul.carlucci@gmail.com>
2020-04-28 23:43 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2 siblings, 1 reply; 22+ messages in thread
From: Paul Carlucci @ 2020-04-28 16:24 UTC (permalink / raw)
To: Rajin Raj <rajin.raj@opsveda.com>; +Cc: pgsql-admin@lists.postgresql.org; pgsql-hackers@postgresql.org
PG the text, character varying, character varying(length), character column
types are all the same thing with each column type inheriting the
properties from the parent type. With each successive type further
properties are added but they're all basically just "text" with some
additional metadata. If you're coming from other database engines or just
general programming languages where text and fixed length string fields are
handled differently then the above can seem a bit different form what
you're used to. Heck, I can think of one engine where if you have a text
column you have to query the table for the blob identifier and then issue a
separate call to retrieve it. Here in PG it's literally all the same,
handled the same, performs the same. Use what limiters make sense for your
application.
On Tue, Apr 28, 2020 at 5:22 AM Rajin Raj <rajin.raj@opsveda.com> wrote:
> Is there any impact of using the character varying without providing the
> length while creating tables?
> I have created two tables and inserted 1M records. But I don't see any
> difference in pg_class. (size, relpage)
>
> create table test_1(name varchar);
> create table test_2(name varchar(50));
>
> insert into test_1 ... 10M records
> insert into test_2 ... 10M records
>
> vacuum (full,analyze) db_size_test_1;
> vacuum (full,analyze) db_size_test_2;
>
> Which option is recommended?
>
> *Regards,*
> *Rajin *
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 16:24 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Paul Carlucci <paul.carlucci@gmail.com>
@ 2020-04-28 23:43 ` raf <raf@raf.org>
2020-04-28 23:49 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 00:20 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
0 siblings, 2 replies; 22+ messages in thread
From: raf @ 2020-04-28 23:43 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Paul Carlucci wrote:
> On Tue, Apr 28, 2020 at 5:22 AM Rajin Raj <rajin.raj@opsveda.com> wrote:
>
> > Is there any impact of using the character varying without providing the
> > length while creating tables?
> > I have created two tables and inserted 1M records. But I don't see any
> > difference in pg_class. (size, relpage)
> >
> > create table test_1(name varchar);
> > create table test_2(name varchar(50));
> >
> > insert into test_1 ... 10M records
> > insert into test_2 ... 10M records
> >
> > vacuum (full,analyze) db_size_test_1;
> > vacuum (full,analyze) db_size_test_2;
> >
> > Which option is recommended?
> >
> > *Regards,*
> > *Rajin *
> >
> PG the text, character varying, character varying(length), character column
> types are all the same thing with each column type inheriting the
> properties from the parent type. With each successive type further
> properties are added but they're all basically just "text" with some
> additional metadata. If you're coming from other database engines or just
> general programming languages where text and fixed length string fields are
> handled differently then the above can seem a bit different form what
> you're used to. Heck, I can think of one engine where if you have a text
> column you have to query the table for the blob identifier and then issue a
> separate call to retrieve it. Here in PG it's literally all the same,
> handled the same, performs the same. Use what limiters make sense for your
> application.
My advice is to never impose arbitrary limits on text.
You will probably regret the choice of limit at some
point. I recently encountered people complaining that
they (thought they) needed to store 21 characters in
a field that they had limited to 10 characters (even
though they were originally told that the recipient
of the data would accept up to 40 characters).
I just use "text" for everything. It's less typing. :-)
The only good reason I can think of for limiting the
length would be to mitigate the risk of some kind of
denial of service, so a limit of 1KiB or 1MiB maybe.
But even that sounds silly. I've never done it (except
to limit CPU usage for slow password hashing but even
then, the 1KiB limit was imposed by input validation,
not by the database schema).
cheers,
raf
P.S. My aversion to arbitrary length limits applies to
postgres identifier names as well. I wish they weren't
limited to 63 characters.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 16:24 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Paul Carlucci <paul.carlucci@gmail.com>
2020-04-28 23:43 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
@ 2020-04-28 23:49 ` raf <raf@raf.org>
1 sibling, 0 replies; 22+ messages in thread
From: raf @ 2020-04-28 23:49 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
raf wrote:
> Paul Carlucci wrote:
>
> > On Tue, Apr 28, 2020 at 5:22 AM Rajin Raj <rajin.raj@opsveda.com> wrote:
> >
> > > Is there any impact of using the character varying without providing the
> > > length while creating tables?
> > > I have created two tables and inserted 1M records. But I don't see any
> > > difference in pg_class. (size, relpage)
> > >
> > > create table test_1(name varchar);
> > > create table test_2(name varchar(50));
> > >
> > > insert into test_1 ... 10M records
> > > insert into test_2 ... 10M records
> > >
> > > vacuum (full,analyze) db_size_test_1;
> > > vacuum (full,analyze) db_size_test_2;
> > >
> > > Which option is recommended?
> > >
> > > *Regards,*
> > > *Rajin *
> > >
> > PG the text, character varying, character varying(length), character column
> > types are all the same thing with each column type inheriting the
> > properties from the parent type. With each successive type further
> > properties are added but they're all basically just "text" with some
> > additional metadata. If you're coming from other database engines or just
> > general programming languages where text and fixed length string fields are
> > handled differently then the above can seem a bit different form what
> > you're used to. Heck, I can think of one engine where if you have a text
> > column you have to query the table for the blob identifier and then issue a
> > separate call to retrieve it. Here in PG it's literally all the same,
> > handled the same, performs the same. Use what limiters make sense for your
> > application.
>
> My advice is to never impose arbitrary limits on text.
> You will probably regret the choice of limit at some
> point. I recently encountered people complaining that
> they (thought they) needed to store 21 characters in
> a field that they had limited to 10 characters (even
> though they were originally told that the recipient
> of the data would accept up to 40 characters).
>
> I just use "text" for everything. It's less typing. :-)
>
> The only good reason I can think of for limiting the
> length would be to mitigate the risk of some kind of
> denial of service, so a limit of 1KiB or 1MiB maybe.
> But even that sounds silly. I've never done it (except
> to limit CPU usage for slow password hashing but even
> then, the 1KiB limit was imposed by input validation,
> not by the database schema).
Sorry, the above is misleading/a bad example. The hash
stored in the database is a fixed reasonable length. It
only varies according to the hashing scheme used. It's
only the unhashed password (that isn't stored anywhere)
that was limited by input validation to limit CPU
usage.
> cheers,
> raf
>
> P.S. My aversion to arbitrary length limits applies to
> postgres identifier names as well. I wish they weren't
> limited to 63 characters.
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 16:24 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Paul Carlucci <paul.carlucci@gmail.com>
2020-04-28 23:43 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
@ 2020-04-29 00:20 ` Rui DeSousa <rui@crazybean.net>
2020-04-29 00:34 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 02:29 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
1 sibling, 2 replies; 22+ messages in thread
From: Rui DeSousa @ 2020-04-29 00:20 UTC (permalink / raw)
To: raf <raf@raf.org>; +Cc: pgsql-admin@lists.postgresql.org
> On Apr 28, 2020, at 7:43 PM, raf <raf@raf.org> wrote:
>
> I just use "text" for everything. It's less typing. :-)
>
Ugh, I see it as sign that the designers of the schema didn’t fully think about the actual requirements or care about them and it usually shows. =
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 16:24 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Paul Carlucci <paul.carlucci@gmail.com>
2020-04-28 23:43 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 00:20 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
@ 2020-04-29 00:34 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 00:40 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
1 sibling, 1 reply; 22+ messages in thread
From: David G. Johnston @ 2020-04-29 00:34 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: raf <raf@raf.org>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Tue, Apr 28, 2020 at 5:21 PM Rui DeSousa <rui@crazybean.net> wrote:
> I just use "text" for everything. It's less typing. :-)
>
> Ugh, I see it as sign that the designers of the schema didn’t fully think
> about the actual requirements or care about them and it usually shows.
>
There are very few situations where a non-arbitrary free-form text field is
going to have a non-arbitrary length constraint - that is also immutable.
Generally, spending time to figure out those rare exceptions is wasted
effort better spent elsewhere. They are also mostly insufficient when used
for their typical "protection" purpose. If you really want protection add
well thought out constraints.
Its less problematic now that increasing the generally arbitrary length
doesn't require a table rewrite but you still need to rebuild dependent
objects.
David J.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 16:24 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Paul Carlucci <paul.carlucci@gmail.com>
2020-04-28 23:43 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 00:20 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 00:34 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) David G. Johnston <david.g.johnston@gmail.com>
@ 2020-04-29 00:40 ` Rui DeSousa <rui@crazybean.net>
2020-04-29 01:22 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Rui DeSousa @ 2020-04-29 00:40 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: raf <raf@raf.org>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Apr 28, 2020, at 8:34 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> Its less problematic now that increasing the generally arbitrary length doesn't require a table rewrite but you still need to rebuild dependent objects.
>
To increase a column length does not require a table rewrite or table scan; however, reducing its size will require a full table scan. So cleaning up a schema like the one proposed sucks. =
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 16:24 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Paul Carlucci <paul.carlucci@gmail.com>
2020-04-28 23:43 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 00:20 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 00:34 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 00:40 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
@ 2020-04-29 01:22 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: David G. Johnston @ 2020-04-29 01:22 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: raf <raf@raf.org>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Tue, Apr 28, 2020 at 5:40 PM Rui DeSousa <rui@crazybean.net> wrote:
> To increase a column length does not require a table rewrite or table
> scan; however, reducing its size will require a full table scan. So
> cleaning up a schema like the one proposed sucks.
>
I estimate the probability of ever desiring to reduce the length of a
varchar field to be indistinguishable from zero.
David J.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 16:24 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Paul Carlucci <paul.carlucci@gmail.com>
2020-04-28 23:43 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 00:20 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
@ 2020-04-29 02:29 ` raf <raf@raf.org>
2020-04-29 04:19 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
1 sibling, 1 reply; 22+ messages in thread
From: raf @ 2020-04-29 02:29 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Rui DeSousa wrote:
> > On Apr 28, 2020, at 7:43 PM, raf <raf@raf.org> wrote:
> >
> > I just use "text" for everything. It's less typing. :-)
>
> Ugh, I see it as sign that the designers of the schema didn’t fully
> think about the actual requirements or care about them and it usually
> shows.
You are mistaken. I care a lot. That's why I
future-proof designs whenever possible by
not imposing arbitrarily chosen limits that
appear to suit current requirements.
In other words, I know I'm not smart enough
to predict the future so I don't let that
fact ruin my software. :-)
cheers,
raf
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 16:24 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Paul Carlucci <paul.carlucci@gmail.com>
2020-04-28 23:43 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 00:20 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 02:29 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
@ 2020-04-29 04:19 ` Rui DeSousa <rui@crazybean.net>
2020-04-29 04:34 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 05:30 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Tim Cross <theophilusx@gmail.com>
0 siblings, 2 replies; 22+ messages in thread
From: Rui DeSousa @ 2020-04-29 04:19 UTC (permalink / raw)
To: raf <raf@raf.org>; +Cc: pgsql-admin@lists.postgresql.org
> On Apr 28, 2020, at 10:29 PM, raf <raf@raf.org> wrote:
>
> Rui DeSousa wrote:
>
>>> On Apr 28, 2020, at 7:43 PM, raf <raf@raf.org> wrote:
>>>
>>> I just use "text" for everything. It's less typing. :-)
>>
>> Ugh, I see it as sign that the designers of the schema didn’t fully
>> think about the actual requirements or care about them and it usually
>> shows.
>
> You are mistaken. I care a lot. That's why I
> future-proof designs whenever possible by
> not imposing arbitrarily chosen limits that
> appear to suit current requirements.
>
> In other words, I know I'm not smart enough
> to predict the future so I don't let that
> fact ruin my software. :-)
>
> cheers,
> raf
>
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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 16:24 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Paul Carlucci <paul.carlucci@gmail.com>
2020-04-28 23:43 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 00:20 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 02:29 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 04:19 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
@ 2020-04-29 04:34 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 04:57 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
1 sibling, 1 reply; 22+ messages in thread
From: David G. Johnston @ 2020-04-29 04:34 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: raf <raf@raf.org>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Tuesday, April 28, 2020, Rui DeSousa <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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 16:24 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Paul Carlucci <paul.carlucci@gmail.com>
2020-04-28 23:43 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 00:20 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 02:29 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 04:19 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 04:34 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) David G. Johnston <david.g.johnston@gmail.com>
@ 2020-04-29 04:57 ` Rui DeSousa <rui@crazybean.net>
2020-04-29 05:09 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Rui DeSousa @ 2020-04-29 04:57 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: raf <raf@raf.org>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> 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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 16:24 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Paul Carlucci <paul.carlucci@gmail.com>
2020-04-28 23:43 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 00:20 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 02:29 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 04:19 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 04:34 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 04:57 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
@ 2020-04-29 05:09 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 05:26 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
0 siblings, 1 reply; 22+ messages in thread
From: David G. Johnston @ 2020-04-29 05:09 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: raf <raf@raf.org>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Tuesday, April 28, 2020, Rui DeSousa <rui@crazybean.net> wrote:
>
> 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 don’t use text instead of varchar(n) for future proofing and use it quite
well within well defined relational schemas. Using varchar(n) in a table
always has a better solution, use text and a constraint.
David J.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 16:24 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Paul Carlucci <paul.carlucci@gmail.com>
2020-04-28 23:43 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 00:20 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 02:29 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 04:19 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 04:34 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 04:57 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 05:09 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) David G. Johnston <david.g.johnston@gmail.com>
@ 2020-04-29 05:26 ` Rui DeSousa <rui@crazybean.net>
2020-04-29 05:32 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Rui DeSousa @ 2020-04-29 05:26 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: raf <raf@raf.org>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> On Apr 29, 2020, at 1:09 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:
> 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 don’t use text instead of varchar(n) for future proofing and use it quite well within well defined relational schemas. Using varchar(n) in a table always has a better solution, use text and a constraint.
>
> David J.
I would agree with you that "text and a constraint" is a lot better than just text; and would be functionally equivalent to varchar(n).
It does requires the reader to look into each constraint to know what’s going on.
Also, when porting the schema to a different database engine and the create table statement fails because it’s too wide and doesn’t fit on a page; the end result is having to go back and redefine the text fields as varchar(n)/char(n) anyway.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 16:24 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Paul Carlucci <paul.carlucci@gmail.com>
2020-04-28 23:43 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 00:20 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 02:29 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 04:19 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 04:34 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 04:57 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 05:09 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 05:26 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
@ 2020-04-29 05:32 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 05:51 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
0 siblings, 1 reply; 22+ messages in thread
From: David G. Johnston @ 2020-04-29 05:32 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: raf <raf@raf.org>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Tuesday, April 28, 2020, Rui DeSousa <rui@crazybean.net> wrote:
>
> I would agree with you that "text and a constraint" is a lot better than
> just text; and would be functionally equivalent to varchar(n).
>
Close enough...
It does requires the reader to look into each constraint to know what’s
> going on.
>
And “n” is so informative...please. The name of the field tells me most
of what I care about, the “n” and/or constraint are fluff.
> Also, when porting the schema to a different database engine and the
> create table statement fails because it’s too wide and doesn’t fit on a
> page; the end result is having to go back and redefine the text fields as
> varchar(n)/char(n) anyway.
>
Not something I’m concerned about and if that other db doesn’t have
something like TOAST it seems like an undesirable target.
David J.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 16:24 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Paul Carlucci <paul.carlucci@gmail.com>
2020-04-28 23:43 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 00:20 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 02:29 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 04:19 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 04:34 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 04:57 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 05:09 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 05:26 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 05:32 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) David G. Johnston <david.g.johnston@gmail.com>
@ 2020-04-29 05:51 ` Rui DeSousa <rui@crazybean.net>
0 siblings, 0 replies; 22+ messages in thread
From: Rui DeSousa @ 2020-04-29 05:51 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: raf <raf@raf.org>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> On Apr 29, 2020, at 1:32 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
>
> And “n” is so informative...please. The name of the field tells me most of what I care about, the “n” and/or constraint are fluff.
>
That was your recommendation; so I’m confused as to why it’s no longer valid.
>
> Also, when porting the schema to a different database engine and the create table statement fails because it’s too wide and doesn’t fit on a page; the end result is having to go back and redefine the text fields as varchar(n)/char(n) anyway.
>
> Not something I’m concerned about and if that other db doesn’t have something like TOAST it seems like an undesirable target.
>
Fine, I assume you will be employed by your employer in perpetuity and the system will remain on PostgreSQL.=
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 16:24 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Paul Carlucci <paul.carlucci@gmail.com>
2020-04-28 23:43 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 00:20 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 02:29 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 04:19 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
@ 2020-04-29 05:30 ` Tim Cross <theophilusx@gmail.com>
2020-04-29 05:52 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
1 sibling, 1 reply; 22+ messages in thread
From: Tim Cross @ 2020-04-29 05:30 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Rui DeSousa <rui@crazybean.net> writes:
>> On Apr 28, 2020, at 10:29 PM, raf <raf@raf.org> wrote:
>>
>> Rui DeSousa wrote:
>>
>>>> On Apr 28, 2020, at 7:43 PM, raf <raf@raf.org> wrote:
>>>>
>>>> I just use "text" for everything. It's less typing. :-)
>>>
>>> Ugh, I see it as sign that the designers of the schema didn’t fully
>>> think about the actual requirements or care about them and it usually
>>> shows.
>>
>> You are mistaken. I care a lot. That's why I
>> future-proof designs whenever possible by
>> not imposing arbitrarily chosen limits that
>> appear to suit current requirements.
>>
>> In other words, I know I'm not smart enough
>> to predict the future so I don't let that
>> fact ruin my software. :-)
>>
>> cheers,
>> raf
>>
>
> 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.
I think the key term in this thread is 'arbitrary'. When implementing a
schema design, it should reflect the known constraints inherent in the
model, but it should avoid imposing arbitrary constraints if none exist
or cannot be determined.
So, if you know that a customer ID field has a current limitation of 50
characters, then use a definition which reflects that. It may be that at
some point in the future, this will be increased, but then again, it may
not and that bit of information provides useful information for
application developers and helps with consistency across APIs. Without
some guideline, different developers will impose different values,
leading to maintenance issues and bugs down the track.
On the other hand, imposing an arbitrary limitation, based on little
more than a guess by the designer, can cause enormous problems. As an
example, I was working on an identity management system where there was
a constraint of 8 characters on the username and password. This was an
arbitrary limit based on what was common practice, but was not a
limitation imposed by any of the systems the IAM system interacted with.
It was recognised that both fields were too small and needed to be
increased. The easy solution would have been to make these fields text.
However, that would cause a problem with some of the systems we needed
to integrate with because either they had a limit on username size or
they had a limit on password size. There were also multiple different
APIs which needed to work with this system and when we performed
analysis, they had varying limits on both fields.
What we did was look at all the systems we had to integrate with and
found the maximum supported username and password lengths for each
system and set the fields to have the maximum length supported by the
systems with the shortest lengths. Having that information in the
database schema also informed those developing other interfaces what the
maximums were. It is quite likely these limits would be increased in the
future and the database definition would need to be increased - in fact,
some years after going into production, exactly this occurred with the
password field when a different encryption algorithm was adopted which
did not have the previous character limitation and the client wanted to
encourage users to use pass phrases rather than a word.
The point is, just using text for all character fields loses information
and results in your model and schema being less expressive. Providing
this information is sometimes critical in ensuring limits are maintained
and provides useful documentation about the model that developers can
use. However, imposing limits based on little more than a guess is
usually a bad idea and if you cannot find any reason to impose a limit,
then don't. I disagree with approaches which claim using text everywhere
is easier and future proofing. In reality, it is just pushing the
problem out for someone else to deal with. The best way to future proof
your application is to have a clear well defined data model that fits
the domain and is well documented and reflected in your database schema.
--
Tim Cross
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 16:24 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Paul Carlucci <paul.carlucci@gmail.com>
2020-04-28 23:43 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 00:20 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 02:29 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) raf <raf@raf.org>
2020-04-29 04:19 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rui DeSousa <rui@crazybean.net>
2020-04-29 05:30 ` Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Tim Cross <theophilusx@gmail.com>
@ 2020-04-29 05:52 ` Rui DeSousa <rui@crazybean.net>
0 siblings, 0 replies; 22+ messages in thread
From: Rui DeSousa @ 2020-04-29 05:52 UTC (permalink / raw)
To: Tim Cross <theophilusx@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> On Apr 29, 2020, at 1:30 AM, Tim Cross <theophilusx@gmail.com> wrote:
>
> I think the key term in this thread is 'arbitrary'. When implementing a
> schema design, it should reflect the known constraints inherent in the
> model, but it should avoid imposing arbitrary constraints if none exist
> or cannot be determined.
>
> So, if you know that a customer ID field has a current limitation of 50
> characters, then use a definition which reflects that. It may be that at
> some point in the future, this will be increased, but then again, it may
> not and that bit of information provides useful information for
> application developers and helps with consistency across APIs. Without
> some guideline, different developers will impose different values,
> leading to maintenance issues and bugs down the track.
>
> On the other hand, imposing an arbitrary limitation, based on little
> more than a guess by the designer, can cause enormous problems. As an
> example, I was working on an identity management system where there was
> a constraint of 8 characters on the username and password. This was an
> arbitrary limit based on what was common practice, but was not a
> limitation imposed by any of the systems the IAM system interacted with.
> It was recognised that both fields were too small and needed to be
> increased. The easy solution would have been to make these fields text.
> However, that would cause a problem with some of the systems we needed
> to integrate with because either they had a limit on username size or
> they had a limit on password size. There were also multiple different
> APIs which needed to work with this system and when we performed
> analysis, they had varying limits on both fields.
>
> What we did was look at all the systems we had to integrate with and
> found the maximum supported username and password lengths for each
> system and set the fields to have the maximum length supported by the
> systems with the shortest lengths. Having that information in the
> database schema also informed those developing other interfaces what the
> maximums were. It is quite likely these limits would be increased in the
> future and the database definition would need to be increased - in fact,
> some years after going into production, exactly this occurred with the
> password field when a different encryption algorithm was adopted which
> did not have the previous character limitation and the client wanted to
> encourage users to use pass phrases rather than a word.
>
> The point is, just using text for all character fields loses information
> and results in your model and schema being less expressive. Providing
> this information is sometimes critical in ensuring limits are maintained
> and provides useful documentation about the model that developers can
> use. However, imposing limits based on little more than a guess is
> usually a bad idea and if you cannot find any reason to impose a limit,
> then don't. I disagree with approaches which claim using text everywhere
> is easier and future proofing. In reality, it is just pushing the
> problem out for someone else to deal with. The best way to future proof
> your application is to have a clear well defined data model that fits
> the domain and is well documented and reflected in your database schema.
>
>
> --
> Tim Cross
>
I can’t agree more… Thanks Tim.
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2020-04-29 05:52 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-28 09:22 PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length) Rajin Raj <rajin.raj@opsveda.com>
2020-04-28 09:43 ` Holger Jakobs <holger@jakobs.com>
2020-04-28 11:46 ` Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
2020-04-28 15:10 ` Holger Jakobs <holger@jakobs.com>
2020-04-28 16:39 ` Ron <ronljohnsonjr@gmail.com>
2020-04-28 16:24 ` Paul Carlucci <paul.carlucci@gmail.com>
2020-04-28 23:43 ` raf <raf@raf.org>
2020-04-28 23:49 ` raf <raf@raf.org>
2020-04-29 00:20 ` Rui DeSousa <rui@crazybean.net>
2020-04-29 00:34 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 00:40 ` Rui DeSousa <rui@crazybean.net>
2020-04-29 01:22 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 02:29 ` raf <raf@raf.org>
2020-04-29 04:19 ` Rui DeSousa <rui@crazybean.net>
2020-04-29 04:34 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 04:57 ` Rui DeSousa <rui@crazybean.net>
2020-04-29 05:09 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 05:26 ` Rui DeSousa <rui@crazybean.net>
2020-04-29 05:32 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-29 05:51 ` Rui DeSousa <rui@crazybean.net>
2020-04-29 05:30 ` Tim Cross <theophilusx@gmail.com>
2020-04-29 05:52 ` Rui DeSousa <rui@crazybean.net>
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