SQLite动态表场景下：如何仅当列存在时查询列值否则返回NULL

Ah, I see the issue here! The problem with your original CASE statement is that SQLite's query parser checks all column references upfront, before executing any logic. So even if your condition correctly identifies that the column doesn't exist, the parser still tries to resolve col_name in the ELSE clause, leading to a syntax error.

Luckily, there's a clever workaround using SQLite's JSON functions that avoids this problem entirely. Here's how to do it:

Solution Code #

SELECT 
  CASE 
    WHEN EXISTS (
      SELECT 1 
      FROM pragma_table_info('your_table_name') 
      WHERE name = 'target_column'
    )
    THEN json_extract(json_object(*), '$.target_column')
    ELSE NULL 
  END AS target_column_value
FROM your_table_name;

How This Works #

Let's break down why this avoids the syntax error:

• pragma_table_info Check: The EXISTS subquery first verifies if the target column exists in the table's schema. This part is safe because it only queries the table metadata, not the table's data.
• JSON Object Trick: Instead of directly referencing target_column, we use json_object(*) to convert the entire row into a JSON object. SQLite accepts * here regardless of which columns exist (as long as the table itself exists).
• Safe Extraction: json_extract then pulls the value for target_column from the JSON object. If the column doesn't exist (even though our CASE condition should prevent this branch from running), json_extract simply returns NULL instead of throwing an error.

Example Usage #

Suppose you have a dynamically generated table user_data that might have columns user_id, full_name, and optionally email. To safely retrieve all three values (with NULL if email is missing):

SELECT 
  user_id,
  full_name,
  CASE 
    WHEN EXISTS (
      SELECT 1 
      FROM pragma_table_info('user_data') 
      WHERE name = 'email'
    )
    THEN json_extract(json_object(*), '$.email')
    ELSE NULL 
  END AS email
FROM user_data;

Handling Case Insensitivity #

If your column names might vary in case (e.g., Email vs email), adjust the query to normalize case:

SELECT 
  CASE 
    WHEN EXISTS (
      SELECT 1 
      FROM pragma_table_info('your_table_name') 
      WHERE LOWER(name) = LOWER('target_column')
    )
    THEN json_extract(
      json_object(*), 
      '$.' || (SELECT name FROM pragma_table_info('your_table_name') WHERE LOWER(name) = LOWER('target_column'))
    )
    ELSE NULL 
  END AS target_column_value
FROM your_table_name;

This ensures you match the column name exactly as it's stored in the schema, avoiding missed extractions due to case differences.

内容的提问来源于stack exchange，提问作者Dharani.C