Skip to content
TheChatArchiveThe Chat Archive
o3-mini· OpenAIHugging Face LMArena Human Preference 140K

how to avoid duckdb create table columns names with quotes on database? (in case,…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Claudeclaude-sonnet-4-20250514’s answer.
  1. User

    how to avoid duckdb create table columns names with quotes on database? (in case, PostgreSQL)

  2. Assistant

    When you see column names “quoted” (for example, appearing as "MyColumn") in DuckDB’s generated DDL or when transferring those definitions to PostgreSQL, it’s usually because the names aren’t valid unquoted SQL identifiers according to the SQL standard. In PostgreSQL (and in many other SQL engines) an unquoted identifier is automatically folded to lower‐case, whereas a quoted identifier preserves its letter case and any special characters. This can lead to issues such as unexpected case sensitivity.

    Here are some guidelines to avoid having DuckDB “quote” your column names:

    1. Use valid, “regular” identifiers:
      Make sure that your column names follow the SQL standard for unquoted identifiers. That means:

      • Use only lowercase letters (a–z), digits (0–9), and the underscore character (_).
      • Do not start with a digit.
      • Avoid spaces or special characters.
      • Do not use reserved keywords (unless you’re absolutely sure, and then you’d have to quote them).

      For example, instead of using a column name like "MyColumn" or "Order", use names like mycolumn or order_number.

    2. Preprocess your names before table creation:
      If your source data (or DataFrame) has column names that are mixed case or have spaces/special characters, convert them to lower-case and remove or replace characters so they conform to the rules. For example, in Python you might do:

      new_columns = [col.lower().replace(" ", "_") for col in df.columns]
      df.columns = new_columns
      

      Then when you create the table, DuckDB (and later PostgreSQL) will not need to quote them.

    3. Explicitly specify columns in your DDL:
      When creating the table, instead of relying on DuckDB’s automatic column name derivation (for example via a CREATE TABLE AS SELECT * FROM ...), write out the CREATE TABLE statement with your chosen (safe) column names. This provides full control over the DDL.

    Why This Matters for PostgreSQL

    • Case Sensitivity: In PostgreSQL, unquoted names are converted to lower case. If you create a table with quoted column names (for example, "MyColumn"), then you must always reference that column with the quotes and exact case. That can be a nuisance or source of bugs.

    • Portability: By using standard, unquoted identifiers (all lowercase, no special characters), your table definitions become more portable between databases that follow these conventions.

    Summary

    DuckDB quotes a column name only when it doesn’t fit the standard “unquoted identifier” pattern. To avoid having quoted names in the generated table definitions (and thus in PostgreSQL):

    • Use lower-case names.
    • Avoid spaces and special characters.
    • Avoid reserved keywords.

    By ensuring that your column names are “safe” identifiers in the first place, you can have DuckDB output DDL without unnecessary quoting, making it easier to work with PostgreSQL and eliminating unexpected case-sensitivity issues.

    Let me know if you need further clarification or additional examples!

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