Transform results of a PostgreSQL query for CSV output

Queries and Resources
1
  • I have a simple database query select * from listings where channel_code = {{select1.value}}
  • I'd like to export the results of this query as CSV format while renaming specific column headers. I want to do this so that the CSV I export matches the expected format/header column names of the downstream system that I will subsequently upload to

I saw this topic here on the Community: Export Data as CSV from JS

But I have the following questions/issues:

  1. When I try to call utils.downloadFile I receive a warning that utils is undefined.
  2. I am trying to write the transformer directly in the "Transform Results" section of the SQL query. What's the correct way to accomplish the above using this feature?

I am not a Javascript expert.

Thanks!

CleanShot 2023-08-15 at 12.10.35
CleanShot 2023-08-15 at 12.10.35744Ă—420 25 KB

2

Hi @zhifengkoh, use the event handlers for download action. Transformer in this instance would require you to return the data of your query.

3

Hi jocen,

What if I need to change the names of the column headers?

4

Hi @zhifengkoh ,

As your querying a postgres db, the easiest way to make the headers match is by changing your query so the columns use aliases.

So instead of select * from table, do:

Select
Column1 as “newcolumnname”,
Column2 as “newname”
From table

Now your result will immediately have the correct columnnames.

5

This is a much easier way to do that... and so DUH. Thank you!

6

As an additional bonus for anyone who finds this post in future, Postgres doesn't have virtual columns like MySQL but it does allow you to generate hard-coded columns on the fly, like so:

select product_name, currency, price, 'ABCXYZ' as "Vendor", '' as "Discount"
from products

Then the resulting table will have a column called "Vendor" with every cell filled with value "ABCXYZ", and a column called "Discount" with every cell filled with a blank value.

I found this useful because I'm trying to use the SQL query to generate a CSV file and the system I am uploading the CSV file to expects certain columns to exist that don't exist in my DB.

1 Like