By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,421 Members | 1,336 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,421 IT Pros & Developers. It's quick & easy.

value too long error

P: n/a
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.

Nov 23 '05 #1
Share this Question
Share on Google+
9 Replies

P: n/a
-----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-----
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2

P: n/a
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 thentruncates 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?

Thanks and Regards,
Rajat.

----- Original Message -----
From: "Uwe C. Schroeder" <uw*@oss4u.com>
To: "Rajat Katyal" <ra****@intelesoftech.com>; <pg***********@postgresql.org>
Sent: Saturday, February 28, 2004 12:46 PM
Subject: Re: [GENERAL] 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-----


Nov 23 '05 #3

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #4

P: n/a
"Rajat Katyal" <ra****@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #5

P: n/a
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 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


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.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #6

P: n/a
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 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


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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #7

P: n/a

Bill Moran <wm****@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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #8

P: n/a
Greg Stark wrote:
Bill Moran <wm****@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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #9

P: n/a
Bill Moran <wm****@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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.