Running your SQL query in Databases

Once you've connected to your SQL database, it's time to write your query and paste it into Geckoboard. Here are a few things you need to know when writing your query:

• Your columns must contain certain data in order to recreate specific visualizations. See table of requirements for each visualization type.
• Queries don't need to be terminated with a semicolon (;).
• Avoid including comments in your SQL query. This includes single line comments using the -- symbol and multi-line comments using the /* and */ symbols.
• You can load data from views as well as tables.
• We have a query execution limit of 15 seconds, after which, the widget will timeout. If you hit this limit, please take steps to simplify your query.
• We limit the size of data returned at 4MB. If you hit this limit, please take steps to simplify your query.

You can check your query syntax with this free SQL query syntax check and validator.

Running your SQL query

To run your SQL query in Databases follow these steps:

Required column data for each visualization type

The following table lists the required data your columns must contain for each of the visualization types:

Visualization

Specification

Number

• The result is a single numeric value.

Gauge

• The result is a single numeric value.

Line Chart

• The first column is a date/datetime (for X-axis labels)
• The remaining columns are all numeric

Column Chart

• The first column is a date, datetime or string (for X-axis labels).
• The remaining columns are all numeric.

Bar Chart

• There are exactly two columns.
• The first column is a date, datetime or string (for Y-axis labels).
• The second column is numeric.

Leaderboard

• There are exactly two columns.
• The first column is a string (for Y-axis labels)
• The second column is numeric.

Table

• There are ten columns or fewer.

Unsupported statements

For security reasons only SELECT statements are allowed, but not all SELECT statements and functions are supported as of yet. The following ones are not currently supported:

• UNION

Unsupported data types

JSONB

JSONB is not supported. To display JSONB data, you'll need to use CAST to convert it to another data type.

SELECT surname, CAST(address as varchar) FROM gecko_keepers WHERE title = 'Dr

SELECT surname, address ->> 'road' AS road FROM gecko_keepers WHERE title = 'Dr

Money

Money is not a supported datatype, and so will not be displayed in visualizations.

Money can be displayed in a table by using CAST to convert from one data type into another.

SELECT destination_id, TRUNC(CAST(value_of_food_money AS NUMERIC)) AS Value, shipping_date from shipping_geckos

UNIX time

UNIX Epoch time is not supported. Only DATE and DATETIME types can be used to build historic visualisations.

Troubleshooting and workarounds

Avoid converting dates into strings

Some functions used for formatting dates are in fact intended to convert the data type from "date" to "char". For example using a query like SELECT TO_CHAR((Subscriptions.ended_at AT TIME ZONE 'UTC'), 'YYYY-MM') AS "month" to get the month in which an event has happened. Instead you can use DATE_TRUNC ('month',Subscriptions.ended_at AT TIME ZONE 'UTC') AS "month" as DATE_TRUNC preserves the datatype as "date"

Minimum and maximum values

The following table illustrates the minimum and maximum values for integer and serial types:

Decimal points

When choosing how many decimal points you'd like, it's best to use the TRUNC function. TRUNC returns a truncated value of a number or a datetime value. For more information, see PostgreSQL's list of Mathematical Functions and Operators.

SELECT date_first_shipped_to, TRUNC(mean_temperature_c,2) as Temperature FROM destinations;

SELECT date_first_shipped_to, TRUNC(gravity) FROM destinations;

Disconnections

Be mindful of creating too many database widgets. The database may disconnect if you exceed approx. 200 widgets.