Searching by date field is not possible using `listSearchableFields` and PostgreSQL

Describe the Bug

Searching through a list by a field of type date set in listSearchableFields throws an error.

It is expected that entering something like “2024-11-23” should work. A date picker would be appreciated.

In addition, searching by date (2024-11-23) for a date and time (2024-11-23T12:24) field should be possible. The "Date in" filter is also broken.

This problem occurs when using the PostgreSQL adapter. It does not occur when using SQLite.

Link to the code that reproduces this issue

https://git.eurostream.de/linus.hamster/payload-bug-search-by-date

Reproduction Steps

Use the PostgreSQL adapter.

Set

  admin: {
    listSearchableFields: ["date"]
  },

in your collection, which contains a date field

[...]
  fields: [
    {
      name: "date",
      type: "date",
    },
[...]

and try to search in this collection from the Admin UI by typing "2024". An error will be thrown.

 GET /admin/collections/games?limit=10&sort=date 200 in 60ms
error: operator does not exist: timestamp with time zone ~~* unknown
    at eval (webpack-internal:///(rsc)/./node_modules/.pnpm/pg-pool@3.7.0_pg@8.11.3/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/.pnpm/drizzle-orm@0.36.1_@types+pg@8.10.2_pg@8.11.3_react@19.0.0-rc-65a56d0e-20241020_types-react@19.0.0-rc.1/node_modules/drizzle-orm/node-postgres/session.js:96:22)
    at async Object.countDistinct (webpack-internal:///(rsc)/./node_modules/.pnpm/@payloadcms+drizzle@3.0.2_@types+pg@8.10.2_payload@3.0.2_graphql@16.9.0_monaco-editor@0.52.0__yuxxnuzibbl5jvanhjbbupfwsy/node_modules/@payloadcms/drizzle/dist/postgres/countDistinct.js:20:25)
    at async find (webpack-internal:///(rsc)/./node_modules/.pnpm/@payloadcms+drizzle@3.0.2_@types+pg@8.10.2_payload@3.0.2_graphql@16.9.0_monaco-editor@0.52.0__yuxxnuzibbl5jvanhjbbupfwsy/node_modules/@payloadcms/drizzle/dist/find/findMany.js:109:21)
    at async findOperation (webpack-internal:///(rsc)/./node_modules/.pnpm/payload@3.0.2_graphql@16.9.0_monaco-editor@0.52.0_react-dom@19.0.0-rc-65a56d0e-20241020_react_fp54bueddpkts7o7cdap7tptuu/node_modules/payload/dist/collections/operations/find.js:116:22)
    at async renderListView (webpack-internal:///(rsc)/./node_modules/.pnpm/@payloadcms+next@3.0.2_graphql@16.9.0_monaco-editor@0.52.0_next@15.0.0_react-dom@19.0.0-rc-65_gdwoz7aioaillroti2i5plvks4/node_modules/@payloadcms/next/dist/views/List/index.js:128:18)
    at async ListView (webpack-internal:///(rsc)/./node_modules/.pnpm/@payloadcms+next@3.0.2_graphql@16.9.0_monaco-editor@0.52.0_next@15.0.0_react-dom@19.0.0-rc-65_gdwoz7aioaillroti2i5plvks4/node_modules/@payloadcms/next/dist/views/List/index.js:234:9) {
  length: 221,
  severity: 'ERROR',
  code: '42883',
  detail: undefined,
  hint: 'No operator matches the given name and argument types. You might need to add explicit type casts.',
  position: '51',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_oper.c',
  line: '647',
  routine: 'op_error'
}
 GET /admin/collections/games?limit=10&page=1&search=2024&sort=date 200 in 72ms

Which area(s) are affected? (Select all that apply)

area: ui, area: core

Environment Info

Payload: 3.0.2

Node.js v20.15.1

Binaries:
  Node: 20.15.1
  npm: 10.7.0
  Yarn: N/A
  pnpm: 9.14.2
Relevant Packages:
  payload: 3.0.2
  next: 15.0.0
  @payloadcms/db-postgres: 3.0.2
  @payloadcms/graphql: 3.0.2
  @payloadcms/next/utilities: 3.0.2
  @payloadcms/richtext-lexical: 3.0.2
  @payloadcms/translations: 3.0.2
  @payloadcms/ui/shared: 3.0.2
  react: 19.0.0-rc-65a56d0e-20241020
  react-dom: 19.0.0-rc-65a56d0e-20241020
Operating System:
  Platform: linux
  Arch: x64
  Version: #1 SMP PREEMPT_DYNAMIC Fri Nov  8 19:23:10 UTC 2024
  Available memory (MB): 31443
  Available CPU cores: 16

Please add a reproduction in order for us to be able to investigate.

Depending on the quality of reproduction steps, this issue may be closed if no reproduction is provided.

Why was this issue marked with the `invalid-reproduction` label?

To be able to investigate, we need access to a reproduction to identify what triggered the issue. We prefer a link to a public GitHub repository created with create-payload-app@beta -t blank or a forked/branched version of this repository with tests added (more info in the reproduction-guide).

To make sure the issue is resolved as quickly as possible, please make sure that the reproduction is as minimal as possible. This means that you should remove unnecessary code, files, and dependencies that do not contribute to the issue. Ensure your reproduction does not depend on secrets, 3rd party registries, private dependencies, or any other data that cannot be made public. Avoid a reproduction including a whole monorepo (unless relevant to the issue). The easier it is to reproduce the issue, the quicker we can help.

Please test your reproduction against the latest version of Payload to make sure your issue has not already been fixed.

I added a link, why was it still marked?

Ensure the link is pointing to a codebase that is accessible (e.g. not a private repository). "example.com", "n/a", "will add later", etc. are not acceptable links -- we need to see a public codebase. See the above section for accepted links.

payloadcms / payload