AW: AW: AW: [HACKERS] Truncation of char, varchar types
Thank you. Is an "exception condition" necessarily an error, or is a warning also an exception condition ? A warning/notice is called a "completion condition". Because other db's only raise a warning. Of course we don't want to copy that behavior if they are not conformant. See above question. Someone said Oracle raises an error. Yes, I am very sorry. Informix seems to be the only other db that truncates silently. Raises a warning instead of error. Would need to check Sybase and DB2, but ... I think Oracle wins here... Yes, good. Do we want this in 7.1.0 ? Seems, yes :-( Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: AW: AW: AW: [HACKERS] Truncation of char, varchar types
Zeugswetter Andreas SB writes: Yes, good. Do we want this in 7.1.0 ? Seems, yes :-( No way. I'm just giving some food for thought while development is slow. In any case there seems to be support for the proposed feature. I'm just waiting for someone to complain that he relies on the existing behaviour, but I doubt that. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
AW: AW: [HACKERS] Truncation of char, varchar types
Excessively long values are currently silently truncated when they are inserted into char or varchar fields. This makes the entire notion of specifying a length limit for these types kind of useless, IMO. Needless to say, it's also not in compliance with SQL. To quote Tom "paragraph and verse please" :-) SQL 1992, 9.2 GR 3 e) """ If the data type of T is variable-length character string and the length in characters M of V is greater than the maximum length in characters L of T, then, Case: i) If the rightmost M-L characters of V are all spaces, then the value of T is set to the first L characters of V and the length in characters of T is set to L. ii) If one or more of the rightmost M-L characters of V are not spaces, then an exception condition is raised: data ^ exception-string data, right truncation. """ Thank you. Is an "exception condition" necessarily an error, or is a warning also an exception condition ? Similarly in SQL 1999 and for other data types. How do people feel about changing this to raise an error in this situation? Can't do. Why not? Because other db's only raise a warning. Of course we don't want to copy that behavior if they are not conformant. See above question. Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: AW: AW: [HACKERS] Truncation of char, varchar types
Zeugswetter Andreas SB writes: Thank you. Is an "exception condition" necessarily an error, or is a warning also an exception condition ? A warning/notice is called a "completion condition". Because other db's only raise a warning. Of course we don't want to copy that behavior if they are not conformant. See above question. Someone said Oracle raises an error. Informix seems to be the only other db that truncates silently. I think Oracle wins here... -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
AW: [HACKERS] Truncation of char, varchar types
Excessively long values are currently silently truncated when they are inserted into char or varchar fields. This makes the entire notion of specifying a length limit for these types kind of useless, IMO. Needless to say, it's also not in compliance with SQL. To quote Tom "paragraph and verse please" :-) How do people feel about changing this to raise an error in this situation? Can't do. Does anybody rely on silent truncation? Yes, iirc the only thing you are allowed to do is issue a warning, but the truncation is allowed and must succeed. (checked in Informix and Oracle) The appropriate SQLSTATE is: "01004" String data, right truncation note that class 01 is a "success with warning". Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: AW: [HACKERS] Truncation of char, varchar types
Zeugswetter Andreas SB writes: Excessively long values are currently silently truncated when they are inserted into char or varchar fields. This makes the entire notion of specifying a length limit for these types kind of useless, IMO. Needless to say, it's also not in compliance with SQL. To quote Tom "paragraph and verse please" :-) SQL 1992, 9.2 GR 3 e) """ If the data type of T is variable-length character string and the length in characters M of V is greater than the maximum length in characters L of T, then, Case: i) If the rightmost M-L characters of V are all spaces, then the value of T is set to the first L characters of V and the length in characters of T is set to L. ii) If one or more of the rightmost M-L characters of V are not spaces, then an exception condition is raised: data ^ exception-string data, right truncation. """ Similarly in SQL 1999 and for other data types. How do people feel about changing this to raise an error in this situation? Can't do. Why not? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [HACKERS] Truncation of char, varchar types
This is what I get in Oracle 8:
SQL CREATE TABLE test (value VARCHAR (10));
Table created.
SQL INSERT INTO test VALUES ('Mike Mascari');
INSERT INTO test VALUES ('Mike Mascari')
*
ERROR at line 1:
ORA-01401: inserted value too large for column
SQL quit
Of course, if the standard is ambiguous, retaining backwards
compatibility sure would be nice.
FWIW,
Mike Mascari
[EMAIL PROTECTED]
-Original Message-
From: Zeugswetter Andreas SB [SMTP:[EMAIL PROTECTED]]
Sent: Tuesday, April 10, 2001 6:47 AM
To: 'Peter Eisentraut'; PostgreSQL Development
Subject:AW: [HACKERS] Truncation of char, varchar types
Excessively long values are currently silently truncated when they
are
inserted into char or varchar fields. This makes the entire notion
of
specifying a length limit for these types kind of useless, IMO.
Needless
to say, it's also not in compliance with SQL.
To quote Tom "paragraph and verse please" :-)
How do people feel about changing this to raise an error in this
situation?
Can't do.
Does anybody rely on silent truncation?
Yes, iirc the only thing you are allowed to do is issue a warning,
but the truncation is allowed and must succeed.
(checked in Informix and Oracle)
The appropriate SQLSTATE is: "01004" String data, right truncation
note that class 01 is a "success with warning".
Andreas
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Truncation of char, varchar types
Nathan Myers writes: We have noticed here also that object (e.g. table) names get truncated in some places and not others. If you create a table with a long name, PG truncates the name and creates a table with the shorter name; but if you refer to the table by the same long name, PG reports an error. This seems odd, because the truncation happens in the scanner. Care to provide a test case? (Very long names may show up in machine- generated schemas.) Would patches for this, e.g. to refuse to create a table with an impossible name, be welcome? Tom Lane is opposed to this, although a number of people seem to like it. Sounds like a configuration option to me. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Truncation of char, varchar types
Excessively long values are currently silently truncated when they are inserted into char or varchar fields. This makes the entire notion of specifying a length limit for these types kind of useless, IMO. Needless to say, it's also not in compliance with SQL. How do people feel about changing this to raise an error in this situation? Does anybody rely on silent truncation? Should this be user-settable, or can those people resort to using triggers? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Truncation of char, varchar types
On Mon, Apr 09, 2001 at 09:20:42PM +0200, Peter Eisentraut wrote: Excessively long values are currently silently truncated when they are inserted into char or varchar fields. This makes the entire notion of specifying a length limit for these types kind of useless, IMO. Needless to say, it's also not in compliance with SQL. How do people feel about changing this to raise an error in this situation? Does anybody rely on silent truncation? Should this be user-settable, or can those people resort to using triggers? Yes, detecting and reporting errors early is a Good Thing. You don't do anybody any favors by pretending to save data, but really throwing it away. We have noticed here also that object (e.g. table) names get truncated in some places and not others. If you create a table with a long name, PG truncates the name and creates a table with the shorter name; but if you refer to the table by the same long name, PG reports an error. (Very long names may show up in machine- generated schemas.) Would patches for this, e.g. to refuse to create a table with an impossible name, be welcome? Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Truncation of char, varchar types
After v7.1 is released ... ?
On Mon, 9 Apr 2001, Peter Eisentraut wrote:
Excessively long values are currently silently truncated when they are
inserted into char or varchar fields. This makes the entire notion of
specifying a length limit for these types kind of useless, IMO. Needless
to say, it's also not in compliance with SQL.
How do people feel about changing this to raise an error in this
situation? Does anybody rely on silent truncation? Should this be
user-settable, or can those people resort to using triggers?
--
Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
