SQL Error: DEFAULT value cannot contain column names
- User
SQL Error: DEFAULT value cannot contain column names
Assistant
You are absolutely right! My apologies. I made a mistake in my previous explanation regarding the
DEFAULTclause.The error message "DEFAULT value cannot contain column names" is correct.
In SQL (and specifically DuckDB), a
DEFAULTvalue 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 AScolumn.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 sourceVARCHARcolumn.dependent_value VARCHAR GENERATED ALWAYS AS (...): This definesdependent_valueas 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 manyWHENclauses as needed to define the different default behaviors based oncategory.STORED: This is important in DuckDB. It means the result of theCASEexpression 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 fordependent_value. DuckDB automatically calculates it based on thecategoryyou 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 ASis the correct and only DDL (Data Definition Language) approach in DuckDB. TheDEFAULTclause is for static or non-dependent default values.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.