Clearly the difference between char and varchar in SQL is that char has
always the same length whereas the length of varchar varies.
To me CHAR(N) is just a shorthand to VARCHAR(N) CHECK(LENGTH(COL) = N)
with the added feature in CHAR that if the length is less than N
it is right padded with spaces.
I would like to see a mention in the documentation what is the default
for N if it is not specified.
If I remember correctly for CHAR it is usually 1.
For varchar the default varies between DBMS products often being the
highest allowable N for VARCHAR
but it can be also 1 (I think this is the case in MSSQL) or it could be
even disallowed to use varchar without precision.
With the present day computing systems I think the most logical way
would be to define type TEXT (MySql, Postgres) or STRING (Java)
that only has a system defined upperbound on the length.
Then varchar(n) could be seen as a shorthand for TEXT CHECK(LENGTH(COL)
<= N)
and char as a shorthand for TEXT CHECK(LENGTH(COL) = N) (although this
does not perform the automatic right-padding).
- rami
On 23.6.2011 21:31, Lukas Eder wrote:
Hello,
I was wondering about H2's behaviour for CHAR types, as documented
here:
http://www.h2database.com/html/datatypes.html#char_type
The documentation says, this type is supported for compatibility with
other databases. To my knowledge, any of these RDBMS show the same
truncation behaviour as H2:
- MySQL (if PAD_CHAR_TO_FULL_LENGTH is not set to true)
- SQLite
- Sybase
But none of these do. For these, a CHAR(3) value seems to always have
3 characters:
- DB2
- Derby
- HSQLDB
- Ingres
- Oracle
- Postgres
- SQL Server
Is there a reason H2 prefers the less popular behaviour of CHAR(n)?
Note, I found this on the roadmap: Compatibility for data type CHAR
(Derby, HSQLDB). Issue 212. Maybe this is the same issue?
Note also, that according to the SQL standard, the correct behaviour
also depends on the collation, which in turn has an effect on ordering
and comparison:
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
Chapter 4.2.1 states:
A collation descriptor includes [...] whether the
collation has the NO PAD or the PAD SPACE
attribute
And then chapter 11.30
<collation definition> ::=
CREATE COLLATION<collation name>
FOR<character set specification>
FROM<collation source> [<pad attribute> ]
<pad attribute> ::= NO PAD | PAD SPACE
Hope this helps!
Cheers
Lukas
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
