What is the Data Type of ''?

[JimKnicely]

As a followup to the "What is the Data Type of NULL" topic discussion (viewtopic.php?f=63&t=682), I found the following information be of interest...

Accroding to the "Vertica 6.1.x Enterprise Edition Release Notes" document, the following change was made to Vertica 6.1:

CREATE TABLE AS SELECT would generate a zero-width column (VARCHAR(0)) if given an empty query result (such as CREATE TABLE example AS SELECT '' AS X;).
This column could cause issues (for example, when exporting the table's design), because VARCHAR columns must be at least 1 character wide. CREATE TABLE AS SELECT now converts any VARCHAR(0) columns into a VARCHAR(80).

Vertica 6.0:

Code: Select all

dbadmin=> select version();
              version
------------------------------------
 Vertica Analytic Database v6.0.0-3
(1 row)

dbadmin=> create table t1 as select '' c1 from dual;
CREATE TABLE
dbadmin=> \d t1;
                                      List of Fields by Tables
    Schema    | Table | Column |    Type    | Size | Default | Not Null | Primary Key | Foreign Key
--------------+-------+--------+------------+------+---------+----------+-------------+-------------
 intersect_wh | t1    | c1     | varchar(0) |    0 |         | f        | f           |
(1 row)

Vertica 6.1:

Code: Select all

dbadmin=> select version();
              version
------------------------------------
 Vertica Analytic Database v6.1.0-0
(1 row)

dbadmin=> create table t1 as select '' c1 from dual;
CREATE TABLE
dbadmin=> \d t1;
                                      List of Fields by Tables
    Schema    | Table | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------------+-------+--------+-------------+------+---------+----------+-------------+-------------
 intersect_wh | t1    | c1     | varchar(80) |   80 |         | f        | f           |
(1 row)

MySQL:

In MySQL we see that a '' will default to a data type of CHAR with a length of 0.

Code: Select all

MySQL> create table t1 as select '' c1;
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

(mydbadmin@localhost) [jim]> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | char(0) | NO   |     |         |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

SQL Server:

SQL Server uses a data type of VARCHAR having a length of 1:



Oracle:

Oracle treats the empty string '' as a CHAR(0):

Code: Select all

SQL> create view t1 as select '' c1 from dual;

View created.

SQL> select data_type, data_length from user_tab_columns where table_name = 'T1';

DATA_TYPE  DATA_LENGTH
---------  -----------
CHAR       0