04:42

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Notes about data types

CHAR and VARCHAR page 2

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;

SQL Server 2008

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 row.

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')

PostgreSQL 8.3

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.

MySQL 5.0

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.

Pages

varchar CHAR DDL

Previous [Notes about data types]

[Float(n)] Next

Last added:

Tags

aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags

The book was updated
several days ago

обменять с capitalist

SQL Problems and solutions

S. I. Moiseenko

Notes about data types

CHAR and VARCHAR page 2