Blob Column Type Omission and Literal Typing in SQLite CTAS Operations

Issue Overview: Blob Column Type Loss and Literal Typing in CREATE TABLE AS SELECT

When utilizing the CREATE TABLE AS SELECT (CTAS) statement in SQLite, developers may encounter unexpected behavior where columns derived from source blob columns lose their explicit type declarations in the newly created table. Additionally, literals used in the SELECT clause (e.g., 1 as file) may not inherit the intended column type affinity. This behavior is rooted in SQLite’s type affinity system, which governs how values are stored and how columns interact with data types.

In the provided example, the orig table contains columns with explicitly declared types: integer, real, text, and blob. When creating the copy table via CTAS, the resulting schema shows that the blob column b becomes typeless, while the integer, real, and text columns retain type affinities (INT, REAL, TEXT). The literal 1 as file also results in a typeless column unless explicitly cast. This discrepancy arises from SQLite’s handling of column affinity during CTAS operations, where the declared type of the source column is not preserved, and literals are assigned minimal type affinity.

Possible Causes: Type Affinity Propagation and Expression-Based Schema Inference

1. Type Affinity Assignment in CTAS

SQLite does not preserve the declared column types of source tables during CTAS. Instead, it infers column affinities based on the expressions in the SELECT statement. For columns directly referencing source table columns (e.g., orig.*), the affinity is derived from the expression type of the column, not its declared type. The blob affinity is not inferred for expressions, even if the source column was explicitly declared as blob.

2. Literal Typing and Minimal Affinity

Literals in SQLite (e.g., 1, 'text') have a storage class (e.g., INTEGER, TEXT) but do not influence column affinity unless explicitly cast. The 1 literal has an INTEGER storage class but results in a column with no declared type (typeless) in CTAS. This is because literals lack a declared type, and SQLite assigns the column the BLOB affinity by default when no type hints are present. However, this behavior is counterintuitive and depends on the expression’s type derivation rules.

3. Blob Affinity Exclusion in Expression Inference

SQLite’s type affinity system prioritizes INTEGER, TEXT, REAL, and NUMERIC affinities. The BLOB affinity is only assigned if the declared type contains the exact string "BLOB" or if the column is explicitly cast. In CTAS, since the SELECT clause does not include declared types, the BLOB affinity is not propagated, even if the source column was declared as blob.

Troubleshooting Steps, Solutions & Fixes: Preserving Column Types in CTAS

1. Explicit Column Casting for Literals and Blobs

To enforce specific type affinities in CTAS, use CAST expressions for literals and columns requiring BLOB affinity:

-- Force "file" column to have INTEGER affinity and "b" column to have BLOB affinity
CREATE TABLE copy AS 
SELECT 
  CAST(1 AS INTEGER) AS file, 
  i AS orig_pk, 
  orig.i, 
  orig.r, 
  orig.t, 
  CAST(orig.b AS BLOB) AS b 
FROM orig;

This ensures the file column has INTEGER affinity and the b column retains BLOB affinity. The CAST operation signals SQLite to assign the desired affinity.

2. Schema Reflection and Dynamic DDL Generation

For a generic solution that preserves source table schema in CTAS, dynamically generate the CREATE TABLE statement using the source table’s schema information:

1. Query PRAGMA table_info(orig) to retrieve column names and types.
2. Construct a CREATE TABLE statement with explicit column types.
3. Use INSERT INTO copy SELECT ... to populate the new table.

Example:

-- Step 1: Create the table with explicit schema
CREATE TABLE copy (
  file INTEGER,
  orig_pk INTEGER,
  i INTEGER,
  r REAL,
  t TEXT,
  b BLOB
);

-- Step 2: Insert data
INSERT INTO copy 
SELECT 1 AS file, i AS orig_pk, orig.* 
FROM orig;

This approach bypasses CTAS limitations by decoupling schema definition from data insertion.

3. Leveraging Temporary Tables for Type Preservation

Use a temporary table to stage data with explicit types before creating the final table:

-- Create a temporary table with desired types
CREATE TEMP TABLE temp_copy AS 
SELECT 
  CAST(1 AS INTEGER) AS file, 
  CAST(i AS INTEGER) AS orig_pk, 
  CAST(r AS REAL) AS r, 
  CAST(t AS TEXT) AS t, 
  CAST(b AS BLOB) AS b 
FROM orig;

-- Create the final table with schema inferred from the temporary table
CREATE TABLE copy AS SELECT * FROM temp_copy;

Temporary tables inherit affinities from the CAST expressions, allowing the final CTAS to preserve types.

4. Using Views to Enforce Type Hints

Create a view with explicit type hints and use it in CTAS:

CREATE VIEW typed_view AS 
SELECT 
  CAST(1 AS INTEGER) AS file, 
  CAST(i AS INTEGER) AS orig_pk, 
  CAST(r AS REAL) AS r, 
  CAST(t AS TEXT) AS t, 
  CAST(b AS BLOB) AS b 
FROM orig;

CREATE TABLE copy AS SELECT * FROM typed_view;

Views propagate the affinities of their constituent expressions, improving type retention in CTAS.

5. Post-CTAS Schema Alteration

Modify the schema after CTAS using ALTER TABLE to add type constraints (note: SQLite’s ALTER TABLE has limitations):

CREATE TABLE copy AS SELECT ... FROM orig;
ALTER TABLE copy ADD COLUMN file INTEGER;
ALTER TABLE copy ADD COLUMN b BLOB;

While this does not retroactively enforce affinity, it documents the intended types for future operations.

6. Custom Functions for Affinity Management

Develop a helper function to generate CTAS-compatible SELECT statements with CAST expressions for all columns:

# Pseudocode for dynamic CAST generation
def generate_ctas_query(source_table):
    columns = get_table_columns(source_table)  # From PRAGMA table_info
    cast_columns = []
    for col in columns:
        if col.type.upper() == 'BLOB':
            cast_columns.append(f"CAST({col.name} AS BLOB) AS {col.name}")
        else:
            cast_columns.append(f"{col.name}")
    return f"CREATE TABLE copy AS SELECT {', '.join(cast_columns)} FROM {source_table}"

This automates the process of preserving affinities for arbitrary source tables.

Conclusion

SQLite’s CTAS behavior is dictated by its type affinity system, which prioritizes expression-derived affinities over declared types. To preserve BLOB affinities and enforce literal typing, explicit CAST operations or schema reflection techniques are necessary. By combining dynamic DDL generation, temporary staging tables, or views, developers can achieve consistent schema replication while mitigating the limitations of direct CTAS usage.