Postgres SQL - GENERATED ALWAYS AS IDENTITY Failing

[dmwest1234]

Environment information

Latest package version -- `npm create amplify@latest`

Describe the bug

When using the following Postgres SQL table using "GENERATED ALWAYS AS IDENTITY", the generated Update and Delete mutations in AppSync are failing.

CREATE TABLE identity (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT
);

However, when using the following below:

CREATE TABLE serial (
    id SERIAL PRIMARY KEY,
    name TEXT
);

Those mutations and queries work fine.

Reproduction steps

1. npm create amplify@latest
2. Create RDS Aurora Serverless cluster using Postgres SQL (default values)
3. Create two tables as described above.
4. npx ampx generate schema-from-database --connection-uri-secret SQL_CONNECTION_STRING --out amplify/data/schema.sql.ts
5. Update /auth/resource.ts file

import { type ClientSchema, a, defineData } from '@aws-amplify/backend';
import { schema as generatedSqlSchema } from './schema.sql';


// Add a global authorization rule
const schema = generatedSqlSchema.authorization(allow => allow.publicApiKey())
export type Schema = ClientSchema<typeof schema>;

export const data = defineData({
  schema,
  authorizationModes: {
    defaultAuthorizationMode: 'apiKey',
    apiKeyAuthorizationMode: {
      expiresInDays: 365
    }
  },
});

6. npx ampx sandbox

Test the mutations and queries for both. Note that the Create mutation requires an id field for the identity table. This fails

error: insert into "identity" ("id", "name") values ($1, $2) returning * - cannot insert a non-DEFAULT value into column "id"

To resolve, I updated the schema.sql.ts file to change .required() to .default() on the id field. This resolves this.

However, Update mutation fails

error: update "identity" set "name" = $1, "id" = $2 where "id" = $3 - column "id" can only be updated to DEFAULT

We can resolve this with custom SQL queries, but this should be resolved by Amplify as "GENERATED ALWAYS AS IDENTITY" is a recommended approach with Postgres SQL over "SERIAL"