Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)

raf wrote:

> Paul Carlucci wrote:
>
> > On Tue, Apr 28, 2020 at 5:22 AM Rajin Raj <rajin(dot)raj(at)opsveda(dot)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.
>