Varchar vs text

The reason I ask is because the documentation says "If character varying is
used without length specifier, the type accepts strings of any size. The
latter is a PostgreSQL extension." I wasn't sure if such an extension meant
there was a level of over-head involved, or reduced its indexability.

2009/1/27 Richard Huxton <dev@archonet.com>

Thom Brown wrote:

The reason I ask is because the documentation says "If character varying is
used without length specifier, the type accepts strings of any size. The
latter is a PostgreSQL extension." I wasn't sure if such an extension meant
there was a level of over-head involved, or reduced its indexability.

No - some other DBs might have different implementations (and so
restrictions) but varchar and text are basically interchangeable. They
are different types though, so if you add custom casts you might want to
do it for both.

There is a limit on btree index size - about 2000 chars if I remember
correctly. That's because you need to fit three index values on an 8KB
page iirc (and there are overheads too).

--
Richard Huxton
Archonet Ltd

I see. Thanks for clarifying!

Thom

2009/1/27 Richard Huxton <dev@archonet.com>

Thom Brown <thombrown@gmail.com> writes:

The reason I ask is because the documentation says "If character varying is
used without length specifier, the type accepts strings of any size. The
latter is a PostgreSQL extension." I wasn't sure if such an extension meant
there was a level of over-head involved, or reduced its indexability.

"Extension" means "it's not in the SQL standard". It's not meant to imply
anything about performance.

There is some potential overhead from using varchar instead of text
because of the extra dummy cast nodes that are likely to be present in
your query expressions (since all the native functions are declared to
take/return text, not varchar). In most cases I'd think you'd be
hard-put to measure any difference though.

regards, tom lane

Thanks for elaborating on that Tom. I understand what it means by extension
now.

The reason I looked into it in the first place was because someone at work
said that varchar was an alias for text, which didn't quite sound right.
And I had automatically used the data-type "text" for any varying text
fields since there is no performance/storage hit in PostgreSQL for such
data, unlike some other RBDMSs. It's interesting to know of the
non-nativity of varchar, even if the practical differences are negligable.
:)

Thanks again

Thom

2009/1/27 Tom Lane <tgl@sss.pgh.pa.us>