Error when inserting into GENERATED ALWAYS AS IDENTITY columns (PostgreSQL)

[stagha]

A standard pattern is for an identity column to be defined like this:

CREATE TABLE IF NOT EXISTS entity
(
    id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,

  -- other columns omitted
)

However, condenser does not appear to be able to handle such columns gracefully. It should be possible to force insert a value in such a column using OVERRIDING SYSTEM VALUE as described here. This is indicated in the error message as well.

Traceback (most recent call last):
  File "direct_subset.py", line 43, in <module>
    subsetter.run_middle_out()
  File "C:\Source\GitHub\TonicAI\condenser\subset.py", line 54, in run_middle_out
    self.__subset_direct(target, relationships)
  File "C:\Source\GitHub\TonicAI\condenser\subset.py", line 117, in __subset_direct
    self.__db_helper.copy_rows(self.__source_conn, self.__destination_conn, q, mysql_db_name_hack(t, self.__destination_conn))
  File "C:\Source\GitHub\TonicAI\condenser\psql_database_helper.py", line 50, in copy_rows
    execute_values(destination_cursor, insert_query, rows, template)
  File "C:\Python36\lib\site-packages\psycopg2\extras.py", line 1299, in execute_values
    cur.execute(b''.join(parts))
psycopg2.errors.GeneratedAlways: cannot insert into column "firm_id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

This issue touches on GENERATED columns, but is not quite the same as it just asks for the ability to exclude such columns from the sub-setting operation. Identity columns are non-null and are key to the foreign key relationships that the tool would preserve, so excluding them would not solve the problem.

[bricct]

Hello, As you can see there's a PR in the works for solving this issue with Generated columns and Generated Identity columns. Hopefully this will merge soon and you won't see this issue anymore.

As a side note, if you'd like to try out our premium database subsetter that has loads more features and does not face this issue, feel free to sign up for a free trial here

[bricct]

This issue should be fixed now!