Overview: Query Builder for PostgreSQL
HOW TO
How to access the Query Builder for PostgreSQL
Navigate to Data Manager > Query Builder to access the Query Builder for PostgreSQL. Click the green + Create Custom Metric button and select your connected PostgreSQL Data Source from the Data Source drop-down list.
-
Query Builder is available in Plus and Business Accounts. Agency Basic Accounts have access to Query Builder in the Agency Account only. Request a trial of Query Builder by following
these steps.
How to use the Query Builder for PostgreSQL
- Daily Values: This Data Type should be selected when your Metric reflects the most up-to-date Daily Value for the Metric. In Databox, the latest daily entry will be displayed for the Custom Metric.
- Total Values: This Data Type should be selected when your Metric reflects the current total Value for the Metric. In Databox, the latest entry will be displayed for the Custom Metric.
For longer Date Ranges, data will be not aggregated and the most recent Value will be displayed for the selected Date Range. This is the main difference between "Total Values" and "Daily Values." - Event Values: This Data Type should be selected when your Metric Value selection reflects individual Value(s) for the Metric at the specified Date/ Timestamp. This Data Type is the default selection because it is appropriate for the majority of use cases.
For longer Date Ranges, individual Metric Values will be aggregated for the entire Date Range and the aggregated Value will be displayed for the Custom Metric.
How to create a Custom PostgreSQL Metric [Example]
The Query Builder for PostgreSQL is designed so you can directly enter SQL queries. For our example, we will use a WordPress PostgreSQL Data Source and create a Custom Metric that returns a list of WordPress authors and their current post count.
SELECT COUNT(p.ID) AS posts, u.display_name, p.date AS date FROM dbwp_users u, dbwp_posts p WHERE p.post_author = u.ID AND p.post_type = 'post' GROUP BY u.ID
AS SQL construct in our query (i.e.
AS posts). This is not mandatory, but it will set the data as a Metric Key. If you use
AS date , this column will represent the Date and Time of the Metric Value (
ISO 8601 Standard Date and Time Format is supported).
If you don’t see any data, double check your SQL query and try it directly in your Database. If no results are displayed there, an error exists in your query. You should also check to confirm that the PostgreSQL user has the necessary permissions to access the Database from Databox's public IP.
How to add a Custom PostgreSQL Metric to a Databoard
Learn how to add a Custom Metric to a Databoard here.