PRAGMA table_info Shows Incorrect Type with Square Brackets in SQLite 3.37+

Understanding Column Type Parsing Errors in PRAGMA table_info

Schema Definition Anomalies and PRAGMA table_info Output Mismatches

Issue Overview
The core problem revolves around unexpected column type formatting in the output of PRAGMA table_info in SQLite versions 3.37.0 and newer. Users observe malformed type strings containing square brackets (]) instead of parentheses (() for columns declared with non-standard type names (e.g., UNSIGNED WORD(1)). For example:

cid | name      | type                | notnull | dflt_value | pk  
1   | bNoWeb    | UNSIGNED WORD](1    | 0       | '0'        | 0  

This issue disrupts applications that programmatically parse PRAGMA table_info to validate schemas or generate code. The root cause lies in how SQLite parses column definitions when non-standard type declarations are wrapped in square brackets. The problem is not a SQLite bug but a consequence of schema design choices that conflict with SQLite’s type affinity rules and identifier quoting mechanisms.

Schema Design Flaws and Identifier Quoting Conflicts

Possible Causes

1. Misuse of Square Brackets in Column Type Declarations
   SQLite interprets square brackets ([ ]) as identifier quotes, not as part of type names. When a column is declared as:

   [Typ] [UNSIGNED WORD](1)
   

   The parser treats [UNSIGNED WORD] as a quoted identifier (the type name), leaving (1) outside the identifier. This results in a malformed type string (UNSIGNED WORD](1) in PRAGMA table_info.

2. Legacy Schema Generation Tools
   Automated tools that generate schemas might inadvertently wrap multi-word type names in square brackets to avoid syntax errors. Over time, this creates inconsistencies, especially when SQLite’s parser behavior evolves (e.g., stricter handling of quoted identifiers in 3.37.0).

3. Mixed Quoting Styles in Composite Schemas
   Schemas mixing quoted and unquoted type declarations (e.g., VARCHAR(10) vs. [UNSIGNED WORD](1)) lead to fragmented parsing outcomes. Columns without brackets return correct type strings, while bracketed ones produce artifacts.

4. Type Affinity Ambiguity
   SQLite ignores most type names and assigns type affinity (e.g., TEXT, NUMERIC). Declarations like UNSIGNED WORD(1) have no inherent meaning to SQLite, making their parsing more susceptible to formatting quirks.

Diagnosing and Resolving Schema Parsing Artifacts

Troubleshooting Steps, Solutions & Fixes

Step 1: Audit Schema Definitions

Extract the exact schema definition using:

SELECT sql FROM sqlite_schema WHERE name='table_name';

Look for patterns like [UNSIGNED WORD](1) or [INTEGER UNSIGNED]. These indicate improper bracketing where the type name and size specifier are split.

Example Problematic Definition:

CREATE TABLE [CBRELEVE] (
  [Typ] [UNSIGNED WORD](1) NOT NULL,  -- Brackets around type name
  [nSeq] [INTEGER UNSIGNED] NOT NULL  -- Brackets around entire type
);

Step 2: Understand Identifier Quoting Rules

• Square brackets ([ ]) and backticks (`) are identifier quotes, not string literals.
• When used in CREATE TABLE, they force SQLite to treat the enclosed text as a single identifier.
• Example:

  CREATE TABLE t1 (
    a [UNSIGNED WORD(1)],  -- Type parsed as "UNSIGNED WORD(1)"
    b [UNSIGNED WORD](1)   -- Type parsed as "UNSIGNED WORD](1"
  );
  

  Column b’s type becomes UNSIGNED WORD](1 because the closing bracket ends the quoted identifier prematurely.

Step 3: Rebuild Schemas with Correct Type Declarations

To fix malformed types:

1. Remove Brackets from Type Names
   Rewrite declarations to use unquoted type names:

   CREATE TABLE CBRELEVE (
     Typ UNSIGNED WORD(1) NOT NULL,
     nSeq INTEGER UNSIGNED NOT NULL
   );
   

   If the type name contains spaces, use double quotes for the entire type:

   CREATE TABLE CBRELEVE (
     Typ "UNSIGNED WORD(1)" NOT NULL  -- Correctly parsed as "UNSIGNED WORD(1)"
   );
   

2. Batch Schema Correction Script
   For legacy databases, use a script to rebuild schemas. Example using sed and sqlite3:

   # Extract and sanitize schema
   sqlite3 old.db .schema | sed -E 's/\[([A-Za-z ]+)\]\(([0-9]+)\)/\1(\2)/g' > new_schema.sql
   
   # Rebuild database
   sqlite3 new.db < new_schema.sql
   

Step 4: Update Schema Generation Tools

Modify automated tools to avoid wrapping type names in brackets. For type names with spaces:

• Use double quotes: "UNSIGNED WORD(1)"
• Use underscores: UNSIGNED_WORD_1

Step 5: Validate PRAGMA table_info Output

After schema fixes, confirm correctness:

PRAGMA table_info(CBRELEVE);

Expected output:

cid | name | type               | notnull | dflt_value | pk  
1   | Typ  | UNSIGNED WORD(1)  | 1       |            | 2  

Step 6: Handle Legacy Data with CHECK Constraints

If UNSIGNED WORD implies domain restrictions (e.g., 0–65535), add CHECK constraints:

CREATE TABLE CBRELEVE (
  Typ INTEGER CHECK (Typ BETWEEN 0 AND 65535)  -- Enforce "UNSIGNED WORD" semantics
);

Step 7: Migrate to Standard SQLite Types

Replace custom types with SQLite’s native types (e.g., INTEGER, TEXT) to avoid parsing ambiguities. For example:

-- Replace "UNSIGNED WORD(1)" with INTEGER
ALTER TABLE CBRELEVE RENAME COLUMN Typ TO Typ_old;
ALTER TABLE CBRELEVE ADD COLUMN Typ INTEGER CHECK (Typ BETWEEN 0 AND 65535);
UPDATE CBRELEVE SET Typ = Typ_old;
ALTER TABLE CBRELEVE DROP COLUMN Typ_old;

Final Notes
The discrepancy in PRAGMA table_info stems from conflating identifier quoting with type declaration formatting. By aligning schema definitions with SQLite’s parsing rules and avoiding non-standard type names, developers ensure consistent metadata across versions. For legacy systems, strategic schema refactoring and tooling updates eliminate parsing artifacts while preserving data integrity.