Postgres Date Issues

Questions
1

Part of my workflow is selecting data from one PG instance, end inserting in another. One of the fields is a timestamp.

When I execute this SQL on the second node:
INSERT INTO waybill_log (waybill_id,status,date_updated,waybill_status_id) VALUES({{$node["Import WAYBILL LOGS"].json["id"]}},$${{$node["Import WAYBILL LOGS"].json["internal_description"]}}$$,$${{$node["Import WAYBILL LOGS"].json["updated"]}}$$,(SELECT id FROM waybill_status WHERE status = $${{$node["Import WAYBILL LOGS"].json["internal_description"]}}$$ LIMIT 1)) RETURNING *;

I get the following error:
“invalid input syntax for type date: "Mon Jul 06 2020 15:14:05 GMT+0000 (Coordinated Universal Time)”

2

This is what is shown in the Result pane of the Expression dialog:

Screenshot 2020-07-29 at 10.03.53
Screenshot 2020-07-29 at 10.03.531802×260 37.5 KB

When I paste this into a SQL client, it executes the insert successfully.

3

The problem is probably that the data is an actual JavaScript-Date-Object. You can try to change your query like this:

INSERT INTO waybill_log (waybill_id,status,date_updated,waybill_status_id) VALUES({{$node["Import WAYBILL LOGS"].json["id"]}},$${{$node["Import WAYBILL LOGS"].json["internal_description"]}}$$,$${{$node["Import WAYBILL `LOGS"].`json["updated"].toISOString()}}$$,(SELECT id FROM waybill_status WHERE status = $${{$node["Import WAYBILL LOGS"].json["internal_description"]}}$$ LIMIT 1)) RETURNING *;

So what I changed is that I added .toISOString() to the date.

2 Likes
4

Snap! I have just tried that, before reading your reply :grinning: … working now! Thanks, @jan

1 Like
5

Great to hear! Have fun!