Postgres date fields error

[haythaminteractivelifelb]

Moved From #7126 (comment) as requested by @r1tsuu

Link to reproduction

No response

Environment Info

Binaries:
  Node: 20.12.2
  npm: N/A
  Yarn: N/A
  pnpm: N/A
Relevant Packages:
  payload: 3.0.0-beta.96
  next: 15.0.0-canary.104
  @payloadcms/db-mongodb: 3.0.0-beta.96
  @payloadcms/db-postgres: 3.0.0-beta.96
  @payloadcms/email-nodemailer: 3.0.0-beta.96
  @payloadcms/graphql: 3.0.0-beta.96
  @payloadcms/live-preview: 3.0.0-beta.96
  @payloadcms/live-preview-react: 3.0.0-beta.96
  @payloadcms/next/utilities: 3.0.0-beta.96
  @payloadcms/plugin-cloud: 3.0.0-beta.96
  @payloadcms/plugin-cloud-storage: 3.0.0-beta.96
  @payloadcms/plugin-form-builder: 3.0.0-beta.96
  @payloadcms/plugin-nested-docs: 3.0.0-beta.96
  @payloadcms/plugin-redirects: 3.0.0-beta.96
  @payloadcms/plugin-seo: 3.0.0-beta.96
  @payloadcms/richtext-lexical: 3.0.0-beta.96
  @payloadcms/storage-s3: 3.0.0-beta.96
  @payloadcms/translations: 3.0.0-beta.96
  @payloadcms/ui/shared: 3.0.0-beta.96
  react: 19.0.0-rc-06d0b89e-20240801
  react-dom: 19.0.0-rc-06d0b89e-20240801
Operating System:
  Platform: win32
  Arch: x64
  Version: Windows 11 Home
  Available memory (MB): 12102
  Available CPU cores: 8

Describe the Bug

I'm on Beta-96 with PostgreSQL 15 Database and I faced this issue from the start in 3.0.0-beta.42 with createdAt and updatedAt fields and I did a simple work around just for these 2 fields assuming that this is related only to this 2 fields. the solution was by adding this hook to all collections:

import type { CollectionBeforeChangeHook } from 'payload'

export const setTimestamps: CollectionBeforeChangeHook<any> = ({ data, collection }) => {
  const currentDate = new Date().toISOString()
  if (!data.createdAt) {
    data.createdAt = currentDate
  } else if (typeof data.createdAt !== 'string' && !(data.createdAt instanceof Date)) {
    data.createdAt = currentDate
  }

  if (!data.updatedAt) {
    data.updatedAt = currentDate
  } else if (typeof data.updatedAt !== 'string' && !(data.updatedAt instanceof Date)) {
    data.updatedAt = currentDate
  }
}

However after adding more date fields this issue came back to the Top as i added 2 new fields

    {
      name: 'one_consent_date',
      type: 'date'
    },
    {
      name: 'two_consent_date',
      type: 'date'
    }

and I was able to save them, at the start i thought that I cannot but after that I noticed that I'm able to save it for some users and not others.

the error is :

error: invalid input syntax for type timestamp with time zone: "{}"
    at eval (webpack-internal:///(rsc)/./node_modules/pg-pool/index.js:45:11)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async eval (webpack-internal:///(rsc)/./node_modules/drizzle-orm/node-postgres/session.js:60:22)

Reproduction Steps

As explained above!
After further debugging I've noticed that the issue is coming from another Date Field resetPasswordExpiration, so if the user resettled his password, he will got this date issue all the time as I cannot find a work around for this field!

Adapters and Plugins

No response

[r1tsuu]

Hey @haythaminteractivelifelb I cannot reproduce it, I added 2 date fields for the users collection, completed the reset password workflow.

Also, for what do you use this hook? I don't see the issue with createdAt, updatedAt as well. But nevertheless, it works ok the same with the hook / without.
There's an issue that related to createdAt when using versions though #7915 but if this hook fixes it for you, you shouldn't apply it to all the collections

Could you clarify what exact steps we need to do to reproduce this?

[haythaminteractivelifelb]

it's happened even on fresh and newly created projects!

Quick question! is it possible that this can be combability issue with PostgreSQL version 15?

if no than! in this case I'll suggest that it will be related to dependencies maybe with drizzle or other packages! here's my package & lock files! i hope this can help a bit:
package-lock.json
package.json

[khlieng]

Getting the same error when calling:

payload.update({
  collection: 'foo',
  id: 1,
  data: {
    title: 'bar'
  }
})

On this collection with 1 doc in it:

{
  slug: 'foo',
  fields: [
    {
      type: 'text',
      name: 'title'
    }
  ]
}

And this hook is enough to fix it:

beforeChange: [
  ({ originalDoc, data }) => {
    if (originalDoc?.createdAt) {
      data.createdAt = originalDoc.createdAt;
    }
  }
]

[r1tsuu]

Quick question! is it possible that this can be combability issue with PostgreSQL version 15?

I don't think so
Please, create a minimal reproduction repo, can be from this repository in the _community test config as well.

[BohdanK-W32]

Hi, there, how it's going on? We're also experiencing the issue

[r1tsuu]

Hey @BohdanK-W32, I couldn't reproduce this at the time. Do you have more details or the same as above? I will try it again soon.

[BohdanK-W32]

Hi, @r1tsuu
Unfortunately, I cannot share the reproduction due to NDA. However I can tell that it happens only during the updates triggered by program logic (so not happening if we use admin UI), on two collections Users and Orders (I think it's applicable to any collection with timestamps).

However the workaround provided above works like a charm

[r1tsuu]

I'd like to see a minimum reproduction repository if it still happens

[nabilfreeman]

@r1tsuu here is a complete reproduction workflow and detailed error message

I was able to replicate this on Postgres 14.10

Reproduction steps:

1. createdb reproduction
2. npx create-payload-app
3. Enter a name (use reproduction for autofill)
4. Choose payload-demo as the project template
5. Choose PostgreSQL (beta) as the database
6. Set the Postgres connection string to postgres://127.0.0.1:5432/reproduction (may be autofilled)
7. Wait for the project to be created
8. npm run dev

Observe the following error:

reproduction » npm run dev

> reproduction@1.0.0 dev
> cross-env PAYLOAD_CONFIG_PATH=src/payload/payload.config.ts nodemon

[nodemon] 3.0.1
[nodemon] to restart at any time, enter `rs`
[nodemon] watching path(s): *.*
[nodemon] watching extensions: ts
[nodemon] starting `ts-node src/server.ts`
[✓] Pulling schema from database...
[15:28:53] INFO (payload): Starting Payload...
[15:28:53] INFO (payload): Payload Admin URL: http://localhost:3000/admin
[15:28:53] INFO (payload): Seeding database...
[15:28:53] INFO (payload): — Clearing media...
[15:28:53] INFO (payload): — Clearing collections and globals...
[15:28:53] INFO (payload): — Seeding demo author and user...
[15:28:54] INFO (payload): — Seeding media...
error: invalid input syntax for type timestamp with time zone: ""
    at /Users/freeman/Projects/reproduction/node_modules/pg/lib/client.js:526:17
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async /Users/freeman/Projects/reproduction/node_modules/src/node-postgres/session.ts:66:19
    at async upsertRow (/Users/freeman/Projects/reproduction/node_modules/@payloadcms/db-postgres/src/upsertRow/index.ts:63:24)
    at async Object.create (/Users/freeman/Projects/reproduction/node_modules/@payloadcms/db-postgres/src/create.ts:17:18)
    at async create (/Users/freeman/Projects/reproduction/node_modules/payload/src/collections/operations/create.ts:253:15) {
  length: 158,
  severity: 'ERROR',
  code: '22007',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: "unnamed portal parameter $4 = ''",
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'datetime.c',
  line: '3803',
  routine: 'DateTimeParseError'
}
Attention: Next.js now collects completely anonymous telemetry regarding usage.
This information is used to shape Next.js' roadmap and prioritize features.
You can learn more, including how to opt-out if you'd not like to participate in this anonymous program, by visiting the following URL:
https://nextjs.org/telemetry

[15:28:54] ERROR (payload): Error seeding database.