Unable to cast string to varchar

Problem

You are trying to cast a string type column to varchar but it isn’t working.

Note

The varchar data type is available in Databricks Runtime 8.0 and above.

  1. Create a simple Delta table, with one column as type string.

    CREATE OR REPLACE TABLE delta_table1 (`col1` string)
    USING DELTA;
    
  2. Use SHOW TABLE on the newly created table and it reports a string type.

    SHOW CREATE TABLE delta_table1;
    
    SHOW CREATE TABLE results shows string type.
  3. Create a second Delta table, based on the first, and convert the string type column into varchar.

    CREATE OR REPLACE TABLE delta_varchar_table1
    USING DELTA
    AS
    SELECT cast(col1 AS VARCHAR(1000)) FROM delta_table1;
    
  4. Use SHOW TABLE on the newly created table and it reports that the table got created, but the column is string type.

    SHOW CREATE TABLE delta_varchar_table1;
    
    SHOW CREATE TABLE results shows string type on table1.

Cause

The varchar type can only be used in table schema. It cannot be used in functions or operators.

Please review the Spark supported data types documentation for more information.

Solution

  1. You cannot cast string to varchar, but you can create a varchar Delta table.

    CREATE OR REPLACE TABLE delta_varchar_table2 (`col1` VARCHAR(1000))
    USING DELTA;
    
  2. Use SHOW TABLE on the newly created table and it reports a varchar type.

    SHOW CREATE TABLE delta_varchar_table2;
    
    SHOW CREATE TABLE results shows varchar type on table2.
  3. You can now create another varchar Delta table, based on the first, and it keeps the varchar type.

    CREATE OR REPLACE TABLE delta_varchar_table3
    USING DELTA
    AS
    SELECT * FROM delta_varchar_table2;
    
  4. Use SHOW TABLE on the newly created table and it reports a varchar type.

    SHOW CREATE TABLE delta_varchar_table3;
    
    SHOW CREATE TABLE results shows varchar type on table3.