Schema Sync Error on Fresh PostgreSQL DB: Attempt to Drop NOT NULL Constraint on Primary Key

[dim garnet]

Hello.

I'm consistently seeing these errors on startup with a fresh PostgreSQL database:

⨯ Error: Failed query: ALTER TABLE "blogs" DROP CONSTRAINT "blogs_id_not_null";
params: 
    at ignore-listed frames {
  query: 'ALTER TABLE "blogs" DROP CONSTRAINT "blogs_id_not_null";',
  params: [],
  payloadInitError: true,
  digest: '2692782',
  [cause]: error: column "id" is in a primary key
      at ignore-listed frames {
    length: 104,
    severity: 'ERROR',
    code: '42P16',
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'tablecmds.c',
    line: '14121',
    routine: 'dropconstraint_internal'
  }
}

Or

Error: Failed query: ALTER TABLE "users_sessions" DROP CONSTRAINT "users_sessions_id_not_null"; params: at ignore-listed frames { query: 'ALTER TABLE "users_sessions" DROP CONSTRAINT "users_sessions_id_not_null";', params: [], payloadInitError: true, digest: '1276317868', [cause]: error: column "id" is in a primary key at ignore-listed frames { length: 104, severity: 'ERROR', code: '42P16', detail: undefined, hint: undefined, position: undefined, internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'tablecmds.c', line: '14121', routine: 'dropconstraint_internal' } } GET / 500 in 5570ms

The app throws a 500 error on first load, but everything works fine after a refresh. I'm not manually defining id in these collections, and I'm using the latest Payload version(3.58.0).

Is this a known issue with schema sync trying to drop NOT NULL constraints on primary keys? Any recommended fix or workaround?

[lofty swan]

New Community-Help Thread Created!

Help is on the way! To mark it as solved, use the /solve command. In the meantime, here are some existing threads that may help you:

Documentation:

• Postgres
• SQLite
• Join Field - Schema advice

Community-Help:

• How Can We Create Custom Field Types in Payload?
• TypeError: Cannot read properties of undefined-if(script.startsWith('migrate'))
• live preview globals disabled page
• Initial Migration Not Happening With Postgres

[upper plover]

Read the error message 😉 It looks like you are trying to drop the not null constraint on the tables primary key. That is certainly not going to work.

[dim garnet]

The primary issue lies in the fact that I do not engage in any manual table modifications. I exclusively utilize the Payload interface for data manipulation, which primarily involves reading and writing operations without any schema alterations.

[dim garnet]

Also, here is the call stack:

[ionic sonnet]

I've been running into a very similar error with a fresh install of payload & postgres 18 - downgrading to postgres 17.6 seemed to fix the issue.

[dim garnet]

That worked for me as well. Thank you so much!