Repository: tobymao/sqlglot

Issue:
AUTOINCREMENT not valid in duckdb
Autoincrement is not supported in duckdb and when transpiling code from another database like snowflake, one gets the following error:

```
duckdb.duckdb.ParserException: Parser Error: syntax error at or near "AUTO_INCREMENT"
```

**Fully reproducible code snippet**
```python
>>> import sqlglot
>>> q = "CREATE TABLE test_table (id NUMERIC NOT NULL AUTOINCREMENT)"
>>> sqlglot.parse_one(q, read="snowflake").sql("duckdb")
'CREATE TABLE test_table (id DECIMAL(38, 0) NOT NULL AUTO_INCREMENT)'
```

And here is a [playground](https://codapi.org/embed/?sandbox=duckdb&code=data%3A%3Bbase64%2Ccw5ydQxxVQhxdPJxVShJLS6JL0lMyklV0MhMUXBxdfb0dfTRMLbQUTDQVPDzD1HwC%2FXxUXAMDfGP9%2FRzDnL1dfUL0QQA) snippet showing the error.

**Official Documentation**
In this [SO](https://stackoverflow.com/questions/72883083/create-an-auto-incrementing-primary-key-in-duckdb) answer they mention that this can be implemented using [sequences](https://duckdb.org/docs/stable/sql/statements/create_sequence.html). The above statement could be converted to something like:

```sql
CREATE SEQUENCE test_table_id_seq START 1;
CREATE TABLE test_table (id DECIMAL(38, 0) NOT NULL DEFAULT NEXTVAL('test_table_id_seq'))
```

Hints from issue comments:
@georgesittas: This is out of scope for SQLGlot, because the transformation is non-trivial and requires >1 statements (state, basically) to be supported, thus breaking the "one statement maps to one statement" invariant.@sk-: @georgesittas I understand that having two statements won't be supported, however the current behavior is unexpected as it returns invalid code for the target database.

I would propose to either:
- raise an error
- return a statement without the `AUTO_INCREMENT` modifier (in the example above it would be `'CREATE TABLE test_table (id DECIMAL(38, 0) NOT NULL)'`)@georgesittas: Sure, I'll make it so this shows as "unsupported.

Solution patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 5a7c20f3..d56201fd 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -1065,3 +1065,7 @@ class DuckDB(Dialect):
 
             self.unsupported("Only integer formats are supported by NumberToStr")
             return self.function_fallback_sql(expression)
+
+        def autoincrementcolumnconstraint_sql(self, _) -> str:
+            self.unsupported("The AUTOINCREMENT column constraint is not supported by DuckDB")
+            return ""


Test patch:
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 12b7c8fa..6bcb967d 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -299,6 +299,13 @@ class TestSnowflake(Validator):
             "SELECT * RENAME (a AS b), c AS d FROM xxx",
         )
 
+        self.validate_all(
+            "CREATE TABLE test_table (id NUMERIC NOT NULL AUTOINCREMENT)",
+            write={
+                "duckdb": "CREATE TABLE test_table (id DECIMAL(38, 0) NOT NULL)",
+                "snowflake": "CREATE TABLE test_table (id DECIMAL(38, 0) NOT NULL AUTOINCREMENT)",
+            },
+        )
         self.validate_all(
             "SELECT TO_TIMESTAMP('2025-01-16 14:45:30.123', 'yyyy-mm-DD hh24:mi:ss.ff6')",
             write={