PostgreSQL node: Invalid time value error if timestamp column contains special datetime values

[riverar]

Summary

n8n fails to return results from a table with a timestamp with timezone column and a row containing -infinity in this column. These are valid PostgreSQL date/time values, per the docs:

8.5.1.4. Special Values
PostgreSQL supports several special date/time input values for convenience, as shown in Table 8.13. The values infinity and -infinity are specially represented inside the system and will be displayed unchanged; but the others are simply notational shorthands that will be converted to ordinary date/time values when read.
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-SPECIAL-VALUES

Error details

{
  "errorMessage": "Invalid time value",
  "errorDescription": "Failed query: SELECT * FROM sss.ttt",
  "errorDetails": {},
  "n8nDetails": {
    "nodeName": "Postgres",
    "nodeType": "n8n-nodes-base.postgres",
    "nodeVersion": 2.3,
    "resource": "database",
    "operation": "executeQuery",
    "time": "3/17/2024, 1:29:54 PM",
    "n8nVersion": "1.33.1 (Self Hosted)",
    "binaryDataMode": "default",
    "stackTrace": [
      "NodeOperationError: Invalid time value",
      "    at parsePostgresError (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/nodes/Postgres/v2/helpers/utils.ts:96:9)",
      "    at /usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/nodes/Postgres/v2/helpers/utils.ts:251:19",
      "    at processTicksAndRejections (node:internal/process/task_queues:95:5)",
      "    at Object.execute (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/nodes/Postgres/v2/actions/database/executeQuery.operation.ts:110:9)",
      "    at Object.router (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/nodes/Postgres/v2/actions/router.ts:40:18)",
      "    at Object.execute (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/nodes/Postgres/v2/PostgresV2.node.ts:26:10)",
      "    at Workflow.runNode (/usr/local/lib/node_modules/n8n/node_modules/n8n-workflow/src/Workflow.ts:1371:8)",
      "    at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/src/WorkflowExecute.ts:1048:29",
      "    at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/src/WorkflowExecute.ts:1724:11"
    ]
  }
}

To Reproduce

1. Create a table with timestamp with timezone column and insert test data.

   CREATE TABLE IF NOT EXISTS test
   (
      ts timestamp with time zone DEFAULT now()
   );
       
   INSERT INTO test(ts) VALUES('-infinity');

2. Create n8n PostgreSQL node that simply SELECT * FROM test

3. Observe error above

Expected behavior

Values to return successfully.

Operating System

Ubuntu 23.10

n8n Version

1.33.1

Node.js Version

18.13.0

Database

SQLite (default)

Execution mode

main (default)

[Joffcom]

Hey @riverar,

Thanks for the report, I have created NODE-1245 as our internal ticket to get this fixed.

[C3Service]

Hi All,

I'm facing the same issue where my n8n abruptly terminated and upon restarting, it's repeating the error "NodeOperationError: Invalid time value at parsePostgresError". Is there anything I can do to resolve this problem?

Node type
n8n-nodes-base.postgres

Node version
2.3 (Latest)

n8n version
1.35.0 (Self Hosted)

[janober]

Fix got released with n8n@1.65.0