SQL Exercises news letters, latest issue

Notes about data types. CHAR and VARCHAR

Let us now see what happened with the definition of data. Below is a test script.

CREATE TABLE Test_char(
chr CHAR,
vchr VARCHAR
);

DELETE FROM Test_char;

INSERT INTO Test_char VALUES
('1','11111111112222222222333333333344444444445555555555');
INSERT INTO Test_char VALUES
('11111111112222222222333333333344444444445555555555', '1');
INSERT INTO Test_char VALUES
('2',CAST('111111111122222222223333333333' AS VARCHAR));
INSERT INTO Test_char VALUES
(CAST('111111111122222222223333333333' AS CHAR), '2');
INSERT INTO Test_char VALUES
('3', '3');

SELECT * FROM Test_char;

chr	vchr  
3	3

So, only one row be inserted containing one character for each column. When you insert the remaining rows we get an error message:

String or binary data would be truncated.
The statement has been terminated.

which means that we should reduce the size of the string.

Although there is standard compliance, it seems to me that there is a contradiction that an explicit cast to the column type of the table does not work:

INSERT INTO Test_char VALUES
(CAST('111111111122222222223333333333' AS CHAR), '2');

chr	vchr  
1	11111111112222222222333333333344444444445555555555
2	111111111122222222223333333333
1	2
3	3

It may be noted the sequence in behavior: VARCHAR is arbitrary size; the second row was not inserted due to an error exceeding the size (ERROR: value too long for type character(1)); explicit value conversion to the column type of the table works, cutting off the extra characters from the right.

VARCHAR type is not supported without specifying the size of the string. CHAR corresponds CHAR (1) - as standard. Since the explicit conversion to CHAR leaves the length of the string without changing, then into the table, defined as

CREATE TABLE Test_char(
chr CHAR,
vchr VARCHAR(1)
);

in the result, as in SQL Server, single row will be added:

chr	vchr 
3	3

Conclusions. In my humble opinion, none of these databases does not meet the standard behavior in those cases where the size of type is not specified. In my opinion, PostgreSQL is most consistent in the "particular implementation". In order to code portability, I would recommend that you always explicitly specify the size.

Useful links

§ All articles from news letters are putting on the site Books and articles about SQL thereafter.