Issue

The following error is returned when seeding a Postgres database where the seeds have values in the identity column.

[PDOException] SQLSTATE[428C9]: <<Unknown error>>: 7 ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override. in /Applications/Develop/webdev/agent-commission/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php on line 346

Versions

Phinx version: 0.13.4
Database: PostgreSQL 14.7

Cause

The reason this error is returned is because the table was created with the identity column having GENERATED ALWAYS through the migration which means that no writes are allowed to this column. Source: https://www.postgresql.org/docs/15/sql-createtable.html

Proposed Solution

Update Create Table

Create the identity column with GENERATED BY DEFAULT, this allows the user specified value to take precedence. Source: https://www.postgresql.org/docs/15/sql-createtable.html

Impacted code:

• https://github.com/cakephp/phinx/blob/0.x/src/Phinx/Db/Table/Column.php#L95

Update Insert

To maintain maximum support the INSERT command could be updated to include OVERRIDING SYSTEM VALUE.

Impacted code:

• https://github.com/cakephp/phinx/blob/0.x/src/Phinx/Db/Adapter/PdoAdapter.php#L269
• https://github.com/cakephp/phinx/blob/0.x/src/Phinx/Db/Adapter/PdoAdapter.php#L272
• https://github.com/cakephp/phinx/blob/0.x/src/Phinx/Db/Adapter/PdoAdapter.php#L319

Example insert statement:

INSERT INTO my_table (id, first_name, last_name)
OVERRIDING SYSTEM VALUE
VALUES (1, 'hello', 'world');