Mause/duckdb_engine

Failure to create non-Identity `integer` PK column due to lack of `SERIAL` support

aaronsteers opened this issue · 4 comments

aaronsteers commented

Related to:

I understand that SERIAL types are not supported, and #289 should eventually add that feature. However, shouldn't an INTEGER column just be created as an integer rather than a SERIAL (auto-identity) column?

We're trying to create primary keys that may or may not be integers, and the failure to create the table in certain circumstance is unexpected.

Two questions:

  1. Is there are workaround to prevent integers from being attempted as SERIAL type columns?
  2. Is there a potential fix where IDENTITY/AUTOINCREMENT/SERIAL columns are just never considered, and the columns are created just as regular ints?

There's probably a lot of missing context on my side, since I'm not deep into Postgres internals. Thanks in advance for your help on this.

Mause commented

Hi @aaronsteers, you can disable this behaviour by setting autoincrement=False on the column declaration itself. This stuff is buried pretty deep inside the PostGres DDL compiler so it isn't straightforward for me to disable it from the DuckDB dialect

aaronsteers commented

Thanks, @Mause!

cc @kgpayne ☝️

aaronsteers commented

@Mause - What do you think of an update to the docs here?: https://github.com/Mause/duckdb_engine#auto-incrementing-id-columns

That section calls out how to do auto-increment in a different way, but not how to just create a "normal" non-incrementing integer column.

I or @kgpayne may be able to assist if helpful.

Mause commented

If you think that documenting it there would be useful sure, I'm happy to accept a pr 😁

  • 👍1