Thread: Varchar vs text
Is there a real difference between a varchar without a specified length and the text datatype?
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:No.
> Is there a real difference between a varchar without a specified length and
> the text datatype?
Wish all questions were that easy :-)
--
Richard Huxton
Archonet Ltd
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
Thom
2009/1/27 Richard Huxton <dev@archonet.com>
Thom Brown wrote:No - some other DBs might have different implementations (and so
> 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.
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
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
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>
Thom Brown <thombrown@gmail.com> writes:"Extension" means "it's not in the SQL standard". It's not meant to imply
> 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.
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