Thread: [NOVICE] varchar vs varchar(n)
john snow <ofbizfanster@gmail.com> writes: > do postgresql developers just use varchar instead of specifying a limit n > when dealing with string types? if so, are there any gotcha's i should be > aware of? Generally speaking, I would only use varchar(n) when there is a clear reason traceable to application requirements why there has to be a limit, and why the limit should be n and not some other number. Otherwise you're just creating issues for yourself. The habit of inventing arbitrary limits on text column width is just a hangover from punched-card days. Actually, Postgres people tend to use "text" rather than unconstrained "varchar". In principle those two types behave equivalently; but the system has to jump through some extra hoops to work with varchar, and every so often you'll run into a case where "varchar" is not optimized as well as "text". regards, tom lane -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
john snow <ofbizfanster@gmail.com> writes:
> do postgresql developers just use varchar instead of specifying a limit n
> when dealing with string types? if so, are there any gotcha's i should be
> aware of?
Generally speaking, I would only use varchar(n) when there is a clear
reason traceable to application requirements why there has to be a
limit, and why the limit should be n and not some other number.
Otherwise you're just creating issues for yourself. The habit of
inventing arbitrary limits on text column width is just a hangover
from punched-card days.
Actually, Postgres people tend to use "text" rather than unconstrained
"varchar". In principle those two types behave equivalently; but the
system has to jump through some extra hoops to work with varchar, and
every so often you'll run into a case where "varchar" is not optimized
as well as "text".
regards, tom lane
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of john snow
Sent: Sunday, November 12, 2017 1:53 PM
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] varchar vs varchar(n)
thanks!
On Sun, Nov 12, 2017 at 2:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
john snow <ofbizfanster@gmail.com> writes:
> do postgresql developers just use varchar instead of specifying a limit n
> when dealing with string types? if so, are there any gotcha's i should be
> aware of?
Generally speaking, I would only use varchar(n) when there is a clear
reason traceable to application requirements why there has to be a
limit, and why the limit should be n and not some other number.
Otherwise you're just creating issues for yourself. The habit of
inventing arbitrary limits on text column width is just a hangover
from punched-card days.
Actually, Postgres people tend to use "text" rather than unconstrained
"varchar". In principle those two types behave equivalently; but the
system has to jump through some extra hoops to work with varchar, and
every so often you'll run into a case where "varchar" is not optimized
as well as "text".
regards, tom lane
[KenB]
Is this true – even if the column in question will be used in (or as part of) an INDEX.
It seems to be the index would work best if the length of the columns involved is a known value.
Ken Benson <Ken@infowerks.com> writes: > On Sun, Nov 12, 2017 at 2:34 PM, Tom Lane <tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>> wrote: > Generally speaking, I would only use varchar(n) when there is a clear > reason traceable to application requirements why there has to be a > limit, and why the limit should be n and not some other number. > Is this true – even if the column in question will be used in (or as part of) an INDEX. > It seems to be the index would work best if the length of the columns involved is a known value. varchar(n) doesn't have a known length. Even char(n) doesn't have a known length in the presence of variable-width text encodings. So there are no optimizations of the sort you're imagining in Postgres. regards, tom lane -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice