Обсуждение: value too long error

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Just don't try to save anything into a field that is too short to store the
value. Doesn't make too much sense, since what doesn't fit into the field
would be cut off anyways. If you need this, have your application either
restrict the input field length or cut the extensive part off before you
store it to the database.


On Friday 27 February 2004 11:00 pm, Rajat Katyal wrote:
> Hi:
>
> Whenever i try to insert the data, size of which is greater than that of
> column datatype size,  I got the exception value too long for.....
>
> However this was not in postgresql7.2.
>
> Can anyone please tell me, is there any way so that i wont get this
> exception. Please help me as soon as possible
>
> Thanks in advance.
>
> Rajat.

- --
    UC

- --
Open Source Solutions 4U, LLC    2570 Fleetwood Drive
Phone:  +1 650 872 2425        San Bruno, CA 94066
Cell:   +1 650 302 2405        United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAQEBcjqGXBvRToM4RAv2TAKC8CmB4/pJWSk7H3/RDjn38RxBM4QCeKl/O
CIf6DMxms1Y81DBd/9lHBwY=
=b4bg
-----END PGP SIGNATURE-----

On Saturday 28 February 2004 08:36, Rajat Katyal wrote:
> Ya, through application we can provide field input validation but iam
> working on the tool which transfers the data from some specifed database to
> another. Here user itself wants if value is too long for the target column
> then truncates the text and insert into the target field. It was there in
> postgres version 7.2 but not in postgres 7.3.
>
> At database level how can we provide a check if text size is greater than
> that of field size then truncate it?

Unfortunately, you can't do this with a BEFORE INSERT trigger, since the
type-checking (which includes length) is done before the trigger will get
called.

You could however:
1. Have a duplicate table, except with unlimited varchar fields and import
into that. Once the batch is in, move it to the destination table with the
relevant substr()
2. You *should* be able to do the same, but with a before trigger that trims,
then inserts to the destination table before returning NULL.
3. You might even be able to use a view with a rule that instead trims the
relevant text fields. Not sure about this one.

--
  Richard Huxton
  Archonet Ltd

"Rajat Katyal" <rajatk@intelesoftech.com> writes:
> Whenever i try to insert the data, size of which is greater than that of co=
> lumn datatype size,  I got the exception value too long for.....=20

> However this was not in postgresql7.2.

IIRC, older versions would just silently truncate the data to the
specified column width.  We concluded that that was not per spec.
7.3 and later make you do it the SQL-spec way, which is to explicitly
truncate the data.  You can do that with a substring operation or by
casting, for instance
    INSERT INTO foo VALUES('an overly long string'::varchar(10));

It's a tad inconsistent that explicit and implicit casts to varchar(N)
act differently, but that's what the SQL spec says to do, AFAICS.
I guess it's reasonable --- the old behavior could result in unintended
data loss.

            regards, tom lane

On Sat, 28 Feb 2004, Rajat Katyal wrote:

> Hi:
>
> Whenever i try to insert the data, size of which is greater than that of column datatype size,  I got the exception
valuetoo long for.....  
>
> However this was not in postgresql7.2.
>
> Can anyone please tell me, is there any way so that i wont get this exception. Please help me as soon as possible

As mentioned earlier, this is against spec (and for good reason,
databases, by default, shouldn't just toss away data that doesn't fit,
they should throw an error and prevent accidental data loss.)

That said, the easiest way to do this is to make the field a text type,
not a limited varchar, then create a before trigger that uses substring to
chop all but the first x characters and insert them.

scott.marlowe wrote:
> On Sat, 28 Feb 2004, Rajat Katyal wrote:
>
>>Hi:
>>
>>Whenever i try to insert the data, size of which is greater than that of column datatype size,  I got the exception
valuetoo long for.....  
>>
>>However this was not in postgresql7.2.
>>
>>Can anyone please tell me, is there any way so that i wont get this exception. Please help me as soon as possible
>
> As mentioned earlier, this is against spec (and for good reason,
> databases, by default, shouldn't just toss away data that doesn't fit,
> they should throw an error and prevent accidental data loss.)
>
> That said, the easiest way to do this is to make the field a text type,
> not a limited varchar, then create a before trigger that uses substring to
> chop all but the first x characters and insert them.

(This just seemed like a good time to do a brain-dump)

I was thinking about this question, and the various answers.

In OO programming, the generally accepted rule is that a program shouldn't
access class values directly, but the class should have methods to set
and retrieve the data.  This allows internal representations to change
without affecting the public API of the class.  It also allows data
validation to occur, if needed.

I'm just wondering how far this rule of thumb could/should be extended to
databases?  I mean, you could say: "Nobody does a direct INSERT, but always
calls a stored procedure that stores the result."  I don't know how much
this might break the mindset of the client developer.

Anyway, it's one possible solution to the problem.  But (to me) it's a
potentially new way of looking at things.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

Bill Moran <wmoran@potentialtech.com> writes:

> In OO programming, the generally accepted rule is that a program shouldn't
> access class values directly, but the class should have methods to set
> and retrieve the data.  This allows internal representations to change
> without affecting the public API of the class.  It also allows data
> validation to occur, if needed.

Note that this is largely a C++ rule. In C++ accessing class members is very
different and much less flexible than using class methods. Other OO languages
are not universally so hobbled.

> I'm just wondering how far this rule of thumb could/should be extended to
> databases?  I mean, you could say: "Nobody does a direct INSERT, but always
> calls a stored procedure that stores the result."  I don't know how much
> this might break the mindset of the client developer.

This is a popular old-school database approach. Personally I find it
incredibly annoying, but I can see its advantages as well. But to me stored
procedures just don't seem like nearly a powerful enough abstraction tool to
make them worth all the pain this approach entails.

--
greg

Greg Stark wrote:
> Bill Moran <wmoran@potentialtech.com> writes:
>
>>In OO programming, the generally accepted rule is that a program shouldn't
>>access class values directly, but the class should have methods to set
>>and retrieve the data.  This allows internal representations to change
>>without affecting the public API of the class.  It also allows data
>>validation to occur, if needed.
>
> Note that this is largely a C++ rule. In C++ accessing class members is very
> different and much less flexible than using class methods. Other OO languages
> are not universally so hobbled.

Really?  I can only assume you're referring to languages such as perl that have
ties (or equivalent capability).  In that case, you're still accessing the data
through a method, it's just a more abstract abstraction.  Or are you referring
to something else?

>>I'm just wondering how far this rule of thumb could/should be extended to
>>databases?  I mean, you could say: "Nobody does a direct INSERT, but always
>>calls a stored procedure that stores the result."  I don't know how much
>>this might break the mindset of the client developer.
>
> This is a popular old-school database approach. Personally I find it
> incredibly annoying, but I can see its advantages as well. But to me stored
> procedures just don't seem like nearly a powerful enough abstraction tool to
> make them worth all the pain this approach entails.

I threw it out there for the sake of discussion.  I can see advantages and
disadvantages.  For example, I'm working on financial software, and
_everything_ is accessed through stored procedures.  This is A Good Think
(in my opinion) because the software is designed to be easily integrated
with other systems.  The last thing we'd want is someone getting the wrong
answer to a financial query because they don't understand the schema.  A
much more reliable way is to have them
SELECT * FROM get_monthy_payment(month, account); so we know they're getting
the correct answer.

But I _can_ see the potential PITA this can cause.  I guess it depends on
the circumstance.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

Bill Moran <wmoran@potentialtech.com> writes:

> Greg Stark wrote:
> >
> > Note that this is largely a C++ rule. In C++ accessing class members is very
> > different and much less flexible than using class methods. Other OO languages
> > are not universally so hobbled.
>
> Really?  I can only assume you're referring to languages such as perl that have
> ties (or equivalent capability).  In that case, you're still accessing the data
> through a method, it's just a more abstract abstraction.  Or are you referring
> to something else?

Well the original motivation is that in C++ member references are handled as C
structure member references which hard code the offset in the structure into
the code. This means nearly any change to your class such as changing member
ordering, changing data types, or adding a member anywhere but at the end
introduces ABI changes that no linker can detect.

Method references on the other hand are resolved by name by the linker. The
linker can resolve problems and you can provide legacy fall-back methods for
old code. So in C++ maintaining a stable ABI is much easier using method calls
than member references.

Languages such as Perl, or Lisp, or most any other OO languages not trying to
maintain C style efficiency resolve member references by name so you can add
or change the "order" of your members without introducing "abi"
incompatibility.

> The last thing we'd want is someone getting the wrong answer to a financial
> query because they don't understand the schema. A much more reliable way is
> to have them
>
> SELECT * FROM get_monthy_payment(month, account); so we know they're getting
> the correct answer.

Of course that doesn't really change the need for the person writing the query
to understand the schema, it just changes which tools that person's working
with. Whoever wrote get_monthy_payment could just as easily get the query
wrong, especially since they seem to be a poor typist :)

You can do the same thing without depending on stored procedures by enforcing
that only low level modules of your application under the control of the same
schema-aware people get to write SQL queries. Upper level modules are only
allowed to call $account->get_monthly_payment($month).

This has the advantage that if get_monthly_payment involves doing several
queries and incorporating out-of-database information it can do so without
forcing people to use awkward database procedural languages or imposing awkard
apis.

--
greg