Truncation of char, varchar, bit, varbit types (fwd)

Implementation proposal as information for the user community. Speak now
or...

---------- Forwarded message ----------
Date: Sat, 19 May 2001 12:33:40 +0200 (CEST)
From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Truncation of char, varchar, bit, varbit types

I've sent this a few weeks ago and got support, I just wanted to issue the
final call.

SQL and Postgres differ in behaviour if the value of a char or varchar
type exceeds the declared length. Postgres cuts off the value, SQL
requires to raise an error.

In particular, the compliant behaviour is:

create table test (a varchar(4));

insert into test values ('ok');
[ok]
insert into test values ('not ok');
ERROR: value too long for type character varying(4)
insert into test values ('good ');
[truncates spaces that are too long]

I think this behaviour is desirable over the old one because it makes the
char and varchar types useful in the first place.

For bit types there is, of course, no such extra rule for spaces.
However, SQL requires that for fixed-width bit strings, the input value
must have as many digits as the declared length of the string. That is,

create table test (a bit(4));
insert into test values (b'101');

will fail. I think that's reasonable, too, because it avoids the
endianness issues.

Unless there are objections, I will make this happen.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://funkturm.homeip.net/~peter

> Implementation proposal as information for the user community. Speak now
> or...
<snip>

FWIW . . .

> insert into test values ('not ok');
> ERROR: value too long for type character varying(4)

/*
* MSSQL 7
*/
create table test (a varchar(4));
The command(s) completed successfully.

insert into test values ('not ok');
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

/*
* Oracle 8.1.6
*/
SQL> create table test (a varchar(4));
Table created.
SQL> insert into test values ('not ok');
insert into test values ('not ok')
*
ERROR at line 1:
ORA-01401: inserted value too large for column

> insert into test values ('good ');
> [truncates spaces that are too long]

/*
* MSSQL 7
*/
insert into test values ('good ');
(1 row(s) affected)
select datalength(a) from test;

-----------
4

(1 row(s) affected)

/*
* Oracle 8.1.6
*/
SQL> insert into test values ('good ');
insert into test values ('good ')
*
ERROR at line 1:
ORA-01401: inserted value too large for column

>
> I think this behaviour is desirable over the old one because it makes the
> char and varchar types useful in the first place.

I agree with your assessment. There are implications for people porting
Oracle applications however.

Regards,

-- Joe

Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> SQL and Postgres differ in behaviour if the value of a char or varchar
> type exceeds the declared length. Postgres cuts off the value, SQL
> requires to raise an error.
>
> In particular, the compliant behaviour is:

> I think this behaviour is desirable over the old one because it makes the
> char and varchar types useful in the first place.

AOL! (Although to ease migration, it would be nice if one could switch
between treating it as an error or a warning for the next few releases).

Ray
--
USDoJ/Judge Jackson: "Microsoft has performed an illegal operation and will
be shut down."
James Turinsky in alt.sysadmin.recovery