Support `SERIAL` column types

[Mause]

Right now duckdb doesn't have native support for serial column types. There are a couple of different ways we could emulate support however

• Generated virtual columns: https://github.com/duckdb/duckdb/pull/3589
• Rewrite columns on the fly into Sequence columns with a default value getter

[WoolenWang]

normal columns like this:

        id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True)

will get error ?

File "D:\tools\python37_x64\python37_x64\lib\site-packages\duckdb_engine_init_.py", line 87, in execute self.c.execute(statement, parameters) │ │ └ () │ └ '\nCREATE TABLE back_test_log (\n\tid SERIAL NOT NULL, \n\tdt TIMESTAMP WITHOUT TIME ZONE NOT NULL, \n\trun_dt TIMESTAMP WITHOUT... └ <duckdb_engine.ConnectionWrapper object at 0x000002B8F4A3F248> RuntimeError: Catalog Error: Type with name SERIAL does not exist! Did you mean "string"?

[Mause]

Also blocked by https://github.com/duckdb/duckdb/issues/1768

[ml31415]

As a temporary workaround, it is possible to hack into the sqlalchemy SQL generation to achieve this. What is required is to replace the following code as seen at https://github.com/duckdb/duckdb/issues/1768#issuecomment-844809673

CREATE TABLE integers(i SERIAL);
-- this statement is equivalent to
CREATE SEQUENCE __internal_serial_sequence_integers_1;
CREATE TABLE integers(i INTEGER DEFAULT(nextval('__internal_serial_sequence_integers_1')));

This code replaces the table generation at the necessary places:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.schema import CreateColumn, CreateTable

@compiles(CreateTable, "duckdb")
def prepend_sequence(element, compiler, **kw):
    """Prepend a table with an auto-incremented primary key with the necessary sequence."""
    table_sql = compiler.visit_create_table(element, **kw)
    table_name = element.element.name
    seq_name = f"__internal_serial_{table_name}"
    if seq_name not in table_sql:
        # no need to create a sequence
        return table_sql
    seq_sql = f"\nCREATE SEQUENCE {seq_name};"
    return seq_sql + table_sql


@compiles(CreateColumn, "duckdb")
def replace_serial_autoincrement(element, compiler, **kw):
    """Replace the first SERIAL field with a duckdb-style auto-incremented integer."""
    column_sql = compiler.visit_create_column(element, **kw)
    if "SERIAL" not in column_sql or not kw.get("first_pk"):
        return column_sql
    table_name = element.element.table.name
    autoinc_type = f"INTEGER DEFAULT(nextval('__internal_serial_{table_name}'))"
    return column_sql.replace("SERIAL", autoinc_type)

This fails if there are multiple SERIALs in one table, otherwise it should be quite specific and hopefully shouldn't cause any troublesome side effects.