Connect Your Spreadsheet to Postgres

Table of Contents

• Connect Row Zero to PostgreSQL
• Write a query to import data
• Build out your connected spreadsheet
• Schedule auto-refresh
• Share connected data
• Export spreadsheet data to Postgres

Connect Row Zero to PostgreSQL

In a Row Zero workbook, click on the 'Data' icon in the upper right-hand corner and then click the button to 'Add connection.'

The Postgres connector requires a few pieces of information defined below.

1. Connection name - This field can be anything that helps identify the data source being connected. (e.g. "Production Data" or "Product Metrics")
2. Host - This is the PostgreSQL address that typically takes the form of a string like 'database.mydomain.com'.
3. Port - This is always 5432.
4. User - This is the username for your PostgreSQL database.
5. Password - The password used to log into your PostgreSQL Database.
6. Database - The name of the PostgreSQL database being connected to.

Once all the information is entered, hit 'Test connection' to ensure the information is correct. If the test connection icon turns green and says 'Connected', proceed by clicking on '+ Add Source.'

*Note - If your database is behind a VPN or firewall, you'll need to allow the Row Zero IP addresses: 18.217.97.112 and 18.224.119.220.

Write a query to import Postgres data

Now that Postgres is connected to your spreadsheet, click the "+" sign next to your Postgres connection to insert a connected table.This adds a data table to your spreadsheet that is connected to Postgres and opens a query editor. Select the 'Schema' and then write a SQL query. The easiest query to write is a 'select *' statement, which pulls in the entire table. For example:

select * from table_name

Click 'Run' to execute the query and import the queried Postgres data into your spreadsheet.The data imports directly into a connected table in the spreadsheet, which provides a dynamic, condensed view of the data and makes it easy to filter, sort, format, pivot, chart, etc. You can also add calculated columns and reference the data in formula functions throughout your workbook. Double-click on the connected table to re-run your Postgres query for the latest data or schedule auto-refresh and everything built on connected data automatically updates and stays in sync.

Build out your Postgres connected spreadsheet

Once connected to Postgres, you can build out your connected spreadsheet or use Row Zero as a powerful postgres GUI to dynamically filter, sort, chart, and pivot Postgres data. Row Zero works like Excel and Google Sheets - just a lot more powerful.

You easily add calculated columns to your connected table by entering a formula in the first column to the right of your table.You can also reference the connected data in formula functions throughout your workbook.Right-click on the connected table to open a context menu with additional options. You can manage columns, cut, copy, create pivot tables, etc.Easily create connected charts by selecting cells in your connected table or pivot table and going to 'Insert', 'Chart' in the header navigation.Once you've built out your spreadsheet, you can double-click on the connected table to re-run your Postgres query for the latest data or schedule auto-updates. This updates everything built on your connected data and keeps your whole spreadsheet in sync with the latest data.

Note, at any point, you can right-click on your connected table and select "Explode table" to expand your data table fully into cells. This allows you to fully edit and delete any cell in the dataset. However, this breaks that data's connection to Postgres, so you won't be able to re-run your query with new data when exploded.

Schedule auto-refresh

To schedule auto-updates, double-click on your connected table to open the editor and click the calendar icon in the top right. Select 'Refresh daily at' and select your time.This is an easy way to automate spreadsheet updates. When your source data updates, everything built on your connected table updates automatically. You can also re-run your Postgres query at any time to get the latest data.

Share connected data

You have multiple options for sharing Postgres connected data in Row Zero. Click the blue 'Share' button in the top right of the workbook to share your connected spreadsheet with teammates and collaborate in real-time. You can also publish your Postgres query as a shared data source to give teammates one-click access to run your Postgres query in their own spreadsheet. This is an easy way to give non-technical users easy access to governed Postgres data.

Export to Postgres

Row Zero also makes it easy to export your spreadsheet to Postgres. To export a sheet or range of cells, simply select the cells you'd like to export, right-click, select Export to, and select your Postgres database.

Select your Postgres database, schema, and name your table. Note, tables must start with "rz_". This is to prevent accidentally overwriting existing tables in your database.

To export a data table, connected table, or pivot table, simply right-click in the table and select Export to. Check out our video tutorial below to see exporting to postgres in action, or view our post for how to use Row Zero to import CSV files to postgres.

Ready to get started?

Connect to Postgres and build Postgres connected spreadsheets that auto-update as source data changes.