Postgresql update bug?

Questions
1

Hi

I’m having some problems updating values in a PostgreSQL table.

With this JSON I can use the insert operation:

{
  "id": 777654140,
  "pipeline": 199237568,
  "status": "created",
  "started_at": null,
  "finished_at": null,
  "failure_reason": null,
  "stage": "package"
}

but if I use the same with the update operation, I’m getting this exception:

ERROR: column “started_at” is of type timestamp with time zone but expression is of type text

I think the underlying problem is that the column is not set to a null value. I have needed to do something similar when using JDBC.

2

@trygvis can you share the structure of the table? I know in MYSQL you can use a describe operation. However, in Postgre seems to be a bit different The PostgreSQL Describe Table Statement | ObjectRocket

3

The relevant column is a “timestamp with time zone” as the error message said, but here is the full create table statement:

create table if not exists XX(
	id bigint primary key,
	pipeline bigint not null references pipeline,
	status text,
 	started_at timestamptz,
 	finished_at timestamptz,
 	failure_reason text,
	stage text
);
4

@trygvis ok, could make it with the update operation. It seems like a cast seems to be needed which is yet not supported. However, I did make it work with the executeQuery operation. Check the example below.

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        250,
        300
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=update \"public\".testing set started_at = '{{$node[\"Date & Time\"].json[\"started_at\"]}}' where id = 1"
      },
      "name": "Postgres",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        980,
        300
      ],
      "credentials": {
        "postgres": "asasasasasas"
      }
    },
    {
      "parameters": {
        "functionCode": "const date = new Date();\n\n\nreturn [{\n\n  json: {\n     id: 1,\n     started_at: date.toUTCString(),\n  }\n}]   "
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        480,
        300
      ]
    },
    {
      "parameters": {
        "value": "={{$node[\"Function\"].json[\"started_at\"]}}",
        "dataPropertyName": "started_at",
        "custom": true,
        "toFormat": "=YYYY-MM-DDTHH:MM:SS",
        "options": {
          "toTimezone": "UTC"
        }
      },
      "name": "Date & Time",
      "type": "n8n-nodes-base.dateTime",
      "typeVersion": 1,
      "position": [
        710,
        300
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Function",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function": {
      "main": [
        [
          {
            "node": "Date & Time",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Date & Time": {
      "main": [
        [
          {
            "node": "Postgres",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
5

Ok, that is a workaround but it still looks like a bug in the update command.

It should not mark the field as text when its value is null, that will solve it directly.

6

Having the same exact issue

7

You can solve this by telling n8n how to cast the column, in the ‘Columns’ field.

Here’s an example:
image

8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.