Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)

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).

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.