Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-flash· GoogleHugging Face LMArena Human Preference 140K

SQL Error: DEFAULT value cannot contain column names

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Amazon Novaamazon.nova-pro-v1:0’s answer.
  1. User

    SQL Error: DEFAULT value cannot contain column names

  2. Gemini Assistant

    You are absolutely right! My apologies. I made a mistake in my previous explanation regarding the DEFAULT clause.

    The error message "DEFAULT value cannot contain column names" is correct.

    In SQL (and specifically DuckDB), a DEFAULT value for a column is evaluated independently and cannot reference other columns within the same table definition. This is a common limitation across many database systems.


    The correct way to achieve a column whose value depends on another column in DuckDB is using a GENERATED ALWAYS AS column.

    This was the first approach I described, and it is indeed the intended solution for your use case.

    Here's the corrected and definitive approach:

    The Correct Approach: GENERATED ALWAYS AS (Generated Column)

    A generated column's value is automatically computed based on an expression that can reference other columns in the same row.

    -- First, if you tried the incorrect CREATE TABLE, you might need to drop it:
    -- DROP TABLE IF EXISTS mytable;
    
    CREATE TABLE mytable (
      category VARCHAR,
      -- This is the column whose value depends on 'category'
      dependent_value VARCHAR GENERATED ALWAYS AS (
        CASE
          WHEN category = 'error' THEN 'ERR-' || category
          WHEN category = 'warning' THEN 'WARN-' || category
          ELSE 'DEFAULT-' || category -- A more generic default if not error/warning
        END
      ) STORED -- 'STORED' means the value is materialized and stored on disk.
              -- This is generally recommended for performance unless the expression is very simple
              -- and you frequently update the source columns.
    );
    

    Explanation:

    • category VARCHAR: Your source VARCHAR column.
    • dependent_value VARCHAR GENERATED ALWAYS AS (...): This defines dependent_value as a generated column. Its value is always derived from the expression provided.
    • CASE WHEN category = 'error' THEN 'ERR-' || category ... END: This is your conditional logic. You can add as many WHEN clauses as needed to define the different default behaviors based on category.
    • STORED: This is important in DuckDB. It means the result of the CASE expression is computed once when the row is inserted or updated, and then stored physically with the data. This is typically more performant for queries, as the value doesn't need to be recomputed every time it's read.

    How it works:

    When you insert data into mytable, you do not specify a value for dependent_value. DuckDB automatically calculates it based on the category you provide.

    INSERT INTO mytable (category) VALUES
    ('error'),
    ('warning'),
    ('info'),
    ('success');
    

    Querying the data:

    SELECT * FROM mytable;
    

    Expected Output:

    ┌───────────┬───────────────────┐
    │ category  │ dependent_value   │
    ├───────────┼───────────────────┤
    │ error     │ ERR-error         │
    │ warning   │ WARN-warning      │
    │ info      │ DEFAULT-info      │
    │ success   │ DEFAULT-success   │
    └───────────┴───────────────────┘
    

    Key Takeaway:

    For defining a column whose value is derived from other columns in the same table, GENERATED ALWAYS AS is the correct and only DDL (Data Definition Language) approach in DuckDB. The DEFAULT clause is for static or non-dependent default values.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.