Обсуждение: varchar lengths

Hi list,

In Oracle I can...
create table a
(
  b varchar2(10 chars)
);
...and then, regardless of character encoding and how much space an
ascii character vs a ö takes, 10 characters will fit there.

If I do say a web-thing in php I have to do horrors like...
if (10 < mb_strlen ($b, '8bit'))
{
     // fail to explain to the user why it's too long
}

I could be liberal with the length and instead do a...
create table a
(
  b varchar(40)
);
...but I like constraints and this isn't a mysql list so I don't have to
motivate that.

Is there anything I've misunderstood? How does the rest of you deal with
this situation?

Thanks,
Marcus

On 21/09/10 10:40, Marcus Engene wrote:
> Hi list,
>
> In Oracle I can...
> create table a
> (
> b varchar2(10 chars)
> );
> ...and then, regardless of character encoding and how much space an
> ascii character vs a ö takes, 10 characters will fit there.

> Is there anything I've misunderstood? How does the rest of you deal with
> this situation?

PostgreSQL actually measures length in characters anyway, so varchar(10)
always holds 10 characters, whatever they are.

You'll need to have the appropriate database encoding for those
characters of course.

--
   Richard Huxton
   Archonet Ltd

I recommend to use TEXT as type for that kind of columns.
99 out of 100 theories about "this value will never be longer then xx
characters" fail in the long run.

And "text", limited only by PostgreSQLs limits, performs as good or
better then varchar(length_limit) The time of "we only can allow n
chars for first name" for performance reasons have gone by, together
with walkmen and VHS.


Harald


--
GHUM GmbH
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607

Amtsgericht Stuttgart, HRB 734971
-
persuadere.
et programmare

On Tuesday, September 21, 2010 07:23:45 Massa, Harald Armin wrote:
> I recommend to use TEXT as type for that kind of columns.
> 99 out of 100 theories about "this value will never be longer then xx
> characters" fail in the long run.
>
> And "text", limited only by PostgreSQLs limits, performs as good or
> better then varchar(length_limit) The time of "we only can allow n
> chars for first name" for performance reasons have gone by, together
> with walkmen and VHS.
>
>
> Harald

Also, if you are absolutely set on a constraint on the length of the text, you
can use a trigger for this and when the constraint changes, and it will, you
simply modify the trigger.

--
Terry Lee Tucker
tel: (336) 372-5432; cell: (336) 404-6897
terry@chosen-ones.org

On 9/21/10 1:29 , Terry Lee Tucker wrote:
> On Tuesday, September 21, 2010 07:23:45 Massa, Harald Armin wrote:
>
>> I recommend to use TEXT as type for that kind of columns.
>> 99 out of 100 theories about "this value will never be longer then xx
>> characters" fail in the long run.
>>
>> And "text", limited only by PostgreSQLs limits, performs as good or
>> better then varchar(length_limit) The time of "we only can allow n
>> chars for first name" for performance reasons have gone by, together
>> with walkmen and VHS.
>>
>>
>> Harald
>>
> Also, if you are absolutely set on a constraint on the length of the text, you
> can use a trigger for this and when the constraint changes, and it will, you
> simply modify the trigger.
>
>
Thanks for your answers!

Richard was completely right of course. I hadn't actually tested this
since 8.0 but now it works splendidly. Apologies for the noise.

I do use text in several places but in some, where it's motivated, I
like to use constrained lengths. May it be tables that hold data that
goes to legacy systems, indexed columns (such as username) or the name
of a product.

Have a lovely tuesday everyone,
Marcus

Arjen,

> You do need to be wary of malicious users who put a first name of a
>few hundred megabytes.

yes, but if that "my first name is a video" hits the database, it is
allready to late, isn't it?

If it is open to the public, input should be sanitized WAY earlier;
and for an internal application: please compare "number of users
trying to insert a video for their first name" to "number of customers
being annoyed as their name is abbreviated"

Harald

--
GHUM GmbH
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607

Amtsgericht Stuttgart, HRB 734971
-
persuadere.
et programmare