Postgres node and postgres trigger node

[gariva]

Is there any place I can find more documentation or explanation of how the new postgres nodes work.

I been having problems using the select query with dates fields as criteria and can’ find examples or enough reference to troubleshoot.

I have also tried using the postgres trigger node but keeps giving an error that says that the user does not own the table even though it created the trigger. There is no mention of this on the available documentation.
thanks in advance.

[MutedJam]

Hi @gariva, perhaps you can share bit more details on your n8n version, database table structure, your workflow, and the exact errors you are facing?

This will hopefully allow us to provide an example of how to get your queries up and running

[gariva]

Hi @MutedJam, sure and thanks for the answer…

We are running postgres on Amazon RDS. engine version 11.16.
n8n is cloud version 0.233.1

Here are a couple of screenshots of what i mean regarding the date fields. The field I am using to filter is a TIMESTAMP WITHOUT TIME ZONE type.

I will give more detail of the postgres trigger question in a different reply
Thanks

[gariva]

@MutedJam regarding the postgres trigger. I just did another experiment using it…
I first went to the db and removed any trace of previous n8n triggers.
Then I ran the postgres trigger on insert event on a specific table.
To my surprise, after pushing the ‘Listen for event’ button it worked and caught when a record was added, so I pushed the button again and waited for it to work. Sadly it did not. I saw how a new record was added to the table with the trigger not reacting to it. I tried stoping the node from listening but it got hung so I reloaded the workflow.
Then i presses the ‘listen for event’ button again. The screen shot attached shows the response I got.
I tried this twice and got the same result.
My Database now has two n8n objects. A trigger on the table and a Trigger function. Will these Stay there even If I never use the trigger node again?
Thanks for your support.

[MutedJam]

Hi @gariva, from testing this I was able to reproduce the problem and am honestly not sure how the Select operation of the Postgres node is supposed to work with regards to timestamps. I’ve asked about this internally and will get back to you once I know more.

In the meantime, perhaps you simply want to use the “Execute Query” operation instead of the “Select” operation? This worked fine when testing it just now, I was parsing your time value using an expression of {{ DateTime.fromFormat('06/28/2023', 'MM/dd/yyyy').toISO() }}:

With regards to your trigger question I wasn’t able to reproduce this but I suppose this might be related to my Postgres role. You probably would have to delete these objects manually. To work around this perhaps you want to avoid using the trigger node and instead use the Schedule trigger combined with the regular Postgres node in order to regularly fetch new rows?

[gariva]

HI @MutedJam Thanks for your answer…I am currently using the old postgress node with the run sql option. I am trying to update to the new node and wanted to try the select option… I will use the Excute Query as you suggest.

Regarding the Trigger Node… I am exited this is available as our operation has to send some alerts as soon a something happens (ie: a record is added to a table). this event might not happen often but when it happens it is important to notify as soon as possible. Is there some more info on its inner workitngs? Can I chat, talk or email someone who participated in its development.
Thanks,
Gariva

[MutedJam]

Hi @gariva, tbh I don’t know much about the workings of the Postgres trigger and use my own polling logic. This also means I don’t have to worry about database permissions and roles beyond being able to read them.

That said, the node was originally added by @agobrecht, perhaps he or @marcus can share more details on where there is any way to make this work with databases where you might not have full privileges.

As for a quick fix in the meantime I suspect something like grant userB to userA; would do the job based on the error message (where userB is the PostgreSQL role you are using in n8n and userA is the owner of alertas_rastreo).

[gariva]

@MutedJam Thanks for the suggestion… I will reach out to @agobrecht and @marcus and see if they can provide more info…
Thanks Gariva