topic Delta table with Varchar column vs string column in Data Engineering

Delta table with Varchar column vs string column

Hongbo — Fri, 30 Jun 2023 13:39:21 GMT

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)

);

Re: Delta table with Varchar column vs string column

erigaud — Mon, 03 Jul 2023 12:44:20 GMT

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

Re: Delta table with Varchar column vs string column

BriceBuso — Mon, 03 Jul 2023 12:55:20 GMT

Thanks for the precision @erigaud !