Обсуждение: 7.2 changes to varchar truncation

Поиск
Список
Период
Сортировка

7.2 changes to varchar truncation

От
"Jeffrey W. Baker"
Дата:
Given a column of type varchar(n), postgres 7.1 allowed me to insert a
string s of length m where m > n by truncating s.  In 7.2, I get an error:
ERROR:  value too long for type character varying(64).  This is annoying
and breaks practically all of my programs.  The fact that this is
mentioned in the docs doesn't make this suck less.

What do I do to get back the old behavior?

If I have to change my datatypes to text or varchar without a limit, I'll
have to drop and reload my databases (again), about which I plan to have a
real bad attitude.

 -jwb


Re: 7.2 changes to varchar truncation

От
Tom Lane
Дата:
"Jeffrey W. Baker" <jwbaker@acm.org> writes:
> What do I do to get back the old behavior?

You don't.  Unfortunately, SQL92 specifies the new behavior.

You might think about declaring the columns plain text and using
triggers to do the truncation, if you really want truncation to a
length limit.

> If I have to change my datatypes to text or varchar without a limit, I'll
> have to drop and reload my databases (again), about which I plan to have a
> real bad attitude.

Consider hacking pg_attribute.atttypmod or pg_attribute.atttypid, if the
reload time is too daunting.  (However, if you failed to reload your
data because of this error, it seems like you aren't in position to
avoid a reload anyway...)

            regards, tom lane

Re: 7.2 changes to varchar truncation

От
Thomas Lockhart
Дата:
> > What do I do to get back the old behavior?
> You don't.  Unfortunately, SQL92 specifies the new behavior.

You can look for the error message in the source code and (perhaps)
revert to the old behavior instead. I'm not sure if other places in the
code were touched to get the new behavior, so ymmv...

                   - Thomas

Re: 7.2 changes to varchar truncation

От
"Ian Harding"
Дата:
This brings up an interesting question, is there a reason to specify n?  In other words, what is the downside of
VARCHARcompared to VARCHAR(n)?  I will have the same problem soon, so I may change all of mine to plain old VARCHAR now
ifit makes sense... 

Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: iharding@tpchd.org

>>> "Jeffrey W. Baker" <jwbaker@acm.org> 12/31/01 02:04PM >>>
Given a column of type varchar(n), postgres 7.1 allowed me to insert a
string s of length m where m > n by truncating s.  In 7.2, I get an error:
ERROR:  value too long for type character varying(64).  This is annoying
and breaks practically all of my programs.  The fact that this is
mentioned in the docs doesn't make this suck less.

What do I do to get back the old behavior?

If I have to change my datatypes to text or varchar without a limit, I'll
have to drop and reload my databases (again), about which I plan to have a
real bad attitude.

 -jwb


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


Re: 7.2 changes to varchar truncation

От
Andrew Sullivan
Дата:
On Mon, Jan 07, 2002 at 10:54:48AM -0800, Ian Harding wrote:
> This brings up an interesting question, is there a reason to specify n?  In other words, what is the downside of
VARCHARcompared to VARCHAR(n)?   

Depends on whether you want to enforce the length.  I belive that the
new behaviour is more SQL-compliant, but someone with access to the
spec might be abe to correct me.

I will have the same problem soon, so I may change all of mine to plain old VARCHAR now if it makes sense...

> >>> "Jeffrey W. Baker" <jwbaker@acm.org> 12/31/01 02:04PM >>>

> If I have to change my datatypes to text or varchar without a limit, I'll
> have to drop and reload my databases (again), about which I plan to have a
> real bad attitude.

AFAIK, 7.2 will require this anyway.  I believe it's part of the
definition of "major release" that it may require a dump and reload.

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110