Documentation Grafana Cloud Connect to data sources Data sources PostgreSQL PostgreSQL query editor

PostgreSQL query editor

Grafana query editors are unique for each data source.

For general information on Grafana query editors, refer to Query editors.

For general information on querying data sources in Grafana, refer to Query and transform data.

The PostgreSQL query editor is located on the Explore page. You can also access the PostgreSQL query editor from a dashboard panel. Click the ellipsis in the upper right of the panel and select Edit.

PostgreSQL query editor components

The PostgreSQL query editor has two modes: Builder and Code.

Builder mode helps you build a query using a visual interface. Code mode allows for advanced querying and offers support for complex SQL query writing.

PostgreSQL Builder mode

The following components will help you build a PostgreSQL query:

• Format - Select a format response from the drop-down for the PostgreSQL query. The default is Table. If you use the Time series format option, one of the columns must be time. Refer to Time series queries for more information.
• Table - Select a table from the drop-down. Tables correspond to the chosen database.
• Data operations - Optional Select an aggregation from the drop-down. You can add multiple data operations by clicking the + sign. Click the garbage can icon to remove data operations.
• Column - Select a column on which to run the aggregation.
• Alias - Optional Add an alias from the drop-down. You can also add your own alias by typing it in the box and clicking Enter. Remove an alias by clicking the X.
• Filter - Toggle to add filters.
• Filter by column value - Optional If you toggle Filter you can add a column to filter by from the drop-down. To filter on more columns, click the + sign to the right of the condition drop-down. You can choose a variety of operators from the drop-down next to the condition. When multiple filters are added you can add an AND operator to display all true conditions or an OR operator to display any true conditions. Use the second drop-down to choose a filter. To remove a filter, click the X button next to that filter’s drop-down. After selecting a date type column, you can choose Macros from the operators list and select timeFilter which will add the $\_\_timeFilter macro to the query with the selected date column.
• Group - Toggle to add Group by column.
• Group by column - Select a column to filter by from the drop-down. Click the +sign to filter by multiple columns. Click the X to remove a filter.
• Order - Toggle to add an ORDER BY statement.
• Order by - Select a column to order by from the drop-down. Select ascending (ASC) or descending (DESC) order.
• Limit - You can add an optional limit on the number of retrieved results. Default is 50.
• Preview - Toggle for a preview of the SQL query generated by the query builder. Preview is toggled on by default.

PostgreSQL Code mode

To create advanced queries, switch to Code mode by clicking Code in the upper right of the editor window. Code mode supports the auto-completion of tables, columns, SQL keywords, standard SQL functions, Grafana template variables, and Grafana macros. Columns cannot be completed before a table has been specified.

Select Table or Time Series as the format. Click the {} in the bottom right to format the query. Click the downward caret to expand the Code mode editor. CTRL/CMD + Return serves as a keyboard shortcut to execute the query.

Macros

You can add macros to your queries to simplify the syntax and enable dynamic elements, such as date range filters.

Table SQL queries

If the Format option is set to Table, you can execute virtually any type of SQL query. The Table panel will automatically display the resulting columns and rows from your query.

You can change or customize the name of a Table panel column by using the SQL keyword AS syntax.

sql Copy

SELECT
  title as "Title",
  "user".login as "Created By",
  dashboard.created as "Created On"
FROM dashboard
INNER JOIN "user" on "user".id = dashboard.created_by
WHERE $__timeFilter(dashboard.created)

Time series queries

Set the Format option to Time series to create and run time series queries.

The examples in this section refer to the data in the following table:

text Copy

+---------------------+--------------+---------------------+----------+
| time_date_time      | value_double | CreatedAt           | hostname |
+---------------------+--------------+---------------------+----------+
| 2020-01-02 03:05:00 | 3.0          | 2020-01-02 03:05:00 | 10.0.1.1 |
| 2020-01-02 03:06:00 | 4.0          | 2020-01-02 03:06:00 | 10.0.1.2 |
| 2020-01-02 03:10:00 | 6.0          | 2020-01-02 03:10:00 | 10.0.1.1 |
| 2020-01-02 03:11:00 | 7.0          | 2020-01-02 03:11:00 | 10.0.1.2 |
| 2020-01-02 03:20:00 | 5.0          | 2020-01-02 03:20:00 | 10.0.1.2 |
+---------------------+--------------+---------------------+----------+

Time series query results are returned in wide data frame format. In the data frame query result, any column, except for time or string-type columns, transforms into value fields. String columns, on the other hand, become field labels.

Example with metric column:

sql Copy

SELECT
  $__timeGroupAlias("time_date_time",'5m'),
  min("value_double"),
  'min' as metric
FROM test_data
WHERE $__timeFilter("time_date_time")
GROUP BY time
ORDER BY time

Data frame result:

text Copy

+---------------------+-----------------+
| Name: time          | Name: min       |
| Labels:             | Labels:         |
| Type: []time.Time   | Type: []float64 |
+---------------------+-----------------+
| 2020-01-02 03:05:00 | 3               |
| 2020-01-02 03:10:00 | 6               |
+---------------------+-----------------+

To customize default series name formatting, refer to Standard options definitions.

Following are time series query examples.

Example using the fill parameter in the $__timeGroupAlias macro to convert null values to be zero instead:

sql Copy

SELECT
  $__timeGroupAlias("createdAt",'5m',0),
  sum(value) as value,
  hostname
FROM test_data
WHERE
  $__timeFilter("createdAt")
GROUP BY time, hostname
ORDER BY time

Based on the data frame result in the following example, the time series panel will generate two series named value 10.0.1.1 and value 10.0.1.2. To display the series names as 10.0.1.1 and 10.0.1.2, use the Standard options definitions display value ${__field.labels.hostname}.

Data frame result:

text Copy

+---------------------+---------------------------+---------------------------+
| Name: time          | Name: value               | Name: value               |
| Labels:             | Labels: hostname=10.0.1.1 | Labels: hostname=10.0.1.2 |
| Type: []time.Time   | Type: []float64           | Type: []float64           |
+---------------------+---------------------------+---------------------------+
| 2020-01-02 03:05:00 | 3                         | 4                         |
| 2020-01-02 03:10:00 | 6                         | 7                         |
+---------------------+---------------------------+---------------------------+

Example with multiple columns:

sql Copy

SELECT
  $__timeGroupAlias("time_date_time",'5m'),
  min("value_double") as "min_value",
  max("value_double") as "max_value"
FROM test_data
WHERE $__timeFilter("time_date_time")
GROUP BY time
ORDER BY time

Data frame result:

text Copy

+---------------------+-----------------+-----------------+
| Name: time          | Name: min_value | Name: max_value |
| Labels:             | Labels:         | Labels:         |
| Type: []time.Time   | Type: []float64 | Type: []float64 |
+---------------------+-----------------+-----------------+
| 2020-01-02 03:04:00 | 3               | 4               |
| 2020-01-02 03:05:00 | 6               | 7               |
+---------------------+-----------------+-----------------+

Templating

Instead of hard coding values like server, application, or sensor names in your metric queries, you can use variables. Variables appear as drop-down select boxes at the top of the dashboard. These drop-downs make it easy to change the data being displayed in your dashboard.

Refer to Templates for an introduction to creating template variables as well as the different types.

Query variable

If you add a Query template variable you can write a PostgreSQL query to retrieve items such as measurement names, key names, or key values, which will be displayed in the drop-down menu.

For example, you can use a variable to retrieve all the values from the hostname column in a table by creating the following query in the templating variable Query setting.

sql Copy

SELECT hostname FROM host

A query can return multiple columns, and Grafana will automatically generate a list based on the query results. For example, the following query returns a list with values from hostname and hostname2.

sql Copy

SELECT host.hostname, other_host.hostname2 FROM host JOIN other_host ON host.city = other_host.city

To use time range dependent macros like $__timeFilter(column) in your query, you must set the template variable’s refresh mode to On Time Range Change.

sql Copy

SELECT event_name FROM event_log WHERE $__timeFilter(time_column)

Another option is a query that can create a key/value variable. The query should return two columns that are named __text and __value. The __text column must contain unique values (if not, only the first value is used). This allows the drop-down options to display a text-friendly name as the text while using an ID as the value. For example, a query could use hostname as the text and id as the value:

sql Copy

SELECT hostname AS __text, id AS __value FROM host

You can also create nested variables. For example, if you have a variable named region, you can configure the hosts variable to display only the hosts within the currently selected region as shown in the following example. If region is a multi-value variable, use the IN operator instead of = to match multiple values.

sql Copy

SELECT hostname FROM host WHERE region IN($region)

Using __searchFilter to filter results in Query Variable

Using __searchFilter in the query field allows the query results to be filtered based on the user’s input in the drop-down selection box. If you do not enter anything, the default value for __searchFilter is %.

Note that you must enclose the __searchFilter expression in quotes as Grafana does not add them automatically.

The following example demonstrates how to use __searchFilter in the query field to enable real-time searching for hostname as the user type in the drop-down selection box.

sql Copy

SELECT hostname FROM my_host WHERE hostname LIKE '$__searchFilter'

Using Variables in Queries

Template variable values are only quoted when the template variable is a multi-value.

If the variable is a multi-value variable, use the IN comparison operator instead of = to match against multiple values.

You can use two different syntaxes:

$<varname> Example with a template variable named hostname:

sql Copy

SELECT
  atimestamp as time,
  aint as value
FROM table
WHERE $__timeFilter(atimestamp) and hostname in($hostname)
ORDER BY atimestamp ASC

[[varname]] Example with a template variable named hostname:

sql Copy

SELECT
  atimestamp as time,
  aint as value
FROM table
WHERE $__timeFilter(atimestamp) and hostname in([[hostname]])
ORDER BY atimestamp ASC

Disabling quoting for multi-value variables

Grafana automatically formats multi-value variables as a quoted, comma-separated string. For example, if server01 and server02 are selected, they are formatted as 'server01', 'server02'. To remove the quotes, enable the CSV formatting option for the variables:

${servers:csv}

Read more about variable formatting options in the Variables documentation.

Annotations

Annotations allow you to overlay rich event information on top of graphs. Add annotation queries via the Dashboard settings > Annotations view.

Example query using a time column with epoch values:

sql Copy

SELECT
  epoch_time as time,
  metric1 as text,
  concat_ws(', ', metric1::text, metric2::text) as tags
FROM
  public.test_data
WHERE
  $__unixEpochFilter(epoch_time)

Example region query using time and timeend columns with epoch values:

sql Copy

SELECT
  epoch_time as time,
  epoch_time_end as timeend,
  metric1 as text,
  concat_ws(', ', metric1::text, metric2::text) as tags
FROM
  public.test_data
WHERE
  $__unixEpochFilter(epoch_time)

Example query using a time column with a native SQL date/time data type:

sql Copy

SELECT
  native_date_time as time,
  metric1 as text,
  concat_ws(', ', metric1::text, metric2::text) as tags
FROM
  public.test_data
WHERE
  $__timeFilter(native_date_time)

Alerting

Use time series queries to create alerts. Table formatted queries aren’t yet supported in alert rule conditions.

For more information regarding alerting refer to the following:

• Alert rules
• Template annotations and labels