Skip to main content
  • Home
  • Get Help
  • How Do I
  • How can I run a PostgreSQL query in Zapier and use the result set for the rest of the Zap?

Hello,

I have a requirement where I need to pull data from a PostgreSQL database and evaluate this data against data I have added to a Zapier table.  To put a bit of context behind it, I need to pull a total count of “credits used” for each customer from Postgresql, and determine if that count is greater than the total number of “credits purchased” from my Zapier table.  Each table has a Customer ID as a common key so I can easily tie them together.  Sorry for the crude explanation.

The challenge I am running in to is that for my first step, which is my PostgreSQL step, I just want to run the same query each time and have it return the results.  I am not looking for a new row, column, update, etc..The only Event that seems logical is “New Row (Custom Query)” due to the fact I need to provide the custom query.

Since most of the time there will not be a new row since the prior run, the Zap will not proceed.  I am not finding a way to simply run a query and use the result set each time for the rest of the Zap.

I think I can create the entire Zap just fine, but cannot get past the first step.

I hope the above makes sense.  Open to any creative alternative suggestions, or any glaring omissions I am not seeing.

Thank you.

Hi @PatrickD 

Good questions.

Perhaps try these Zap steps:

  1. Trigger: Schedule
  2. Action: PostgreSQL - Find Row via Custom Query

 

Hi @Troy Tessalone - Thanks for such a prompt reply.

I do not have the “Find Row via Custom Query” option.  This is all I have:  

 

@PatrickD 

This would be as an action step.

Perhaps try these Zap steps:

  1. Trigger: Schedule
  2. Action: PostgreSQL - Find Row via Custom Query

@Troy Tessalone - Roger that.  Thanks for pointing that out, and that seems to work.

Running in to another challenge with the Zapier Table but I’ll keep plugging away and create a new post if needed.  Thank you very much for the push.

@Troy Tessalone - embarrassingly enough, I am not sure if this gets me to the finish line.  I found additional information in the community from around a year ago (you were a participant), and apparently this option only returns a single row.  Can you confirm?  I am not looking to return a single row, rather I need to return the entire result set of the query.  

There are 3 columns in the result set - Customer ID, Customer Name, and Flight Count.  In my Zapier Table, I have Customer ID, and Flight Allowance (amongst a few other columns).  The goal is each time the query runs and returns the results, it checks the Zapier table and compares the Flight Count value from the first query to the Flight Allowance value in the Zapier table.  If the Flight Count is > Flight Allowance, that’s my trigger and I want to be notified.

Am I still stuck, or do you have any other creative (or just built in functionality I’m missing) ideas?  I should also mention i’m not married to the Zapier table.  I could house the flight allowance info in a spreadsheet for all I care.

Thanks,

@PatrickD 

You likely won’t be able to return the entire result set of the query as that could be thousands of records.

 

The action description states “a row”, which would be 1 row.

 

You may want to think about doing the inverse to trigger the automations.

Trigger from an updated record in the Zapier Table.

Then find the PostgreSQL.

Proceed to update the Zapier Table record.

 

I’d probably recommend using Airtable instead of Zapier Tables.

Airtable has Views.

Views can have Filters.

Views can be used to trigger Zaps.

Additional Airtable Fields can be used to get records to move in and out of Views. (e.g. Formula field)

Thanks @Troy Tessalone - I’ll check out the above.

Also, not concerned about the size of the record set.  It will always be quite small (a few dozen to a hundred or so records)

Thanks again.

@PatrickD 

FYI: Even a few hundred records can cause issues in Zaps.

Sometimes the size of the data payload returned is too large for Zaps to handle.

The Looping app has a max of 500 items it can process.

Terms & Conditions