Notes about data types

Recently I happened to find an error in the decision, which included the conversion:

CAST(model AS VARCHAR)

Those who have studied the scheme "Computers", think about the absurdity of the type conversion in the same type (the column ‘model’ is defined as VARCHAR (50)). However, it is this conversion, and made the request wrong.

The fact is that, if the type size is not specified when converting, then SQL Server is set to defaults, which for VARCHAR are 30. Thus, if the converted string has larger size, then cut off all the characters except for the first 30. Of course, any errors will not occur. Just on the "long" model numbers the proposed decision and gave the wrong result. As they say in such cases, read the documentation. However, it is interesting that on this occasion Standard said?

In according to standard, if the size is not counted for CHAR and VARCHAR types, it means CHAR(1) and VARCHAR(1) respectively. Let's check DBMS available to me, according to the standard: SQL Server, MySQL, PostgreSQL.

Here there are two aspects:

1. Type conversion

2. Using types CHAR / VARCHAR to describe the schema (DDL)

SQL Server 2008

Console

Execute

SELECT CAST('11111111112222222222333333333344444444445555555555' AS CHAR) chr, 
CAST('11111111112222222222333333333344444444445555555555' AS VARCHAR) vchr

SELECT CAST('11111111112222222222333333333344444444445555555555' AS CHAR) chr, CAST('11111111112222222222333333333344444444445555555555' AS VARCHAR) vchr

    Scheme

No scheme

The result:

That is both a character types truncated to defaults, which are 30. No error messages occur that, in fact, meet the standard.

PostgreSQL 8.3

SELECT CAST('11111111112222222222333333333344444444445555555555' AS CHAR) AS chr,
 CAST('11111111112222222222333333333344444444445555555555' AS VARCHAR) AS vchr

There is a half-hearted compliance with standard, i.e. match it against the type CHAR. With regard to the type VARCHAR, then according to the documentation, if character varying is used without length specifier, the type accepts strings of any size, ie truncation does not occur.

MySQL 5.0

As the saying goes, the farther, the "curiouser". It appears that in MySQL, the conversion of type VARCHAR not supported at all. It remains to check only the conversion to CHAR:

SELECT CAST('11111111112222222222333333333344444444445555555555' AS CHAR) chr

Ie string is not truncated, while in the documentation I read: “When n is not specified when using the CAST and CONVERT functions, the default length is 30”.