Delta table with Varchar column vs string column

Hongbo
New Contributor III

‎06-30-2023 06:39 AM

Databricks support string data type. But I can still create delta table with varchar data type. Just wonder what is different between delta table with string and delta table with varchar:

-- delta table with string

CREATE TABLE persons(first_name STRING NOT NULL, last_name STRING NOT NULL, nickname STRING,
                       CONSTRAINT person_pk PRIMARY KEY (first_name, last_name)
                       );
-- delta table with varchar
CREATE TABLE persons(first_name STRING NOT NULL, last_name varchar(50) NOT NULL, nickname varchar(50),
                       CONSTRAINT person_pk PRIMARY KEY (first_name, last_name)
                       );

erigaud
Honored Contributor

‎07-03-2023 05:44 AM

VARCHAR allows you to specify the size of the string expected in the column. 

This is useful when you know your column cannot exceed a set size (ie for a name, a code etc).

It is equivalent to a CHECK contraint on the size. Trying to insert a value that exceeds the size given results in the following error : 

com.databricks.sql.transaction.tahoe.schema.DeltaInvariantViolationException: Exceeds char/varchar type length limitation. Failed check: (isnull('last_name) OR (length('last_name) <= 50)).
 

View solution in original post

BriceBuso
Contributor II
In response to erigaud

‎07-03-2023 05:55 AM

Thanks for the precision @erigaud ! 

0 Kudos