This is a Pro data source. If not available on your plan please upgrade or contact our support team.
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 astables
.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:
In the pop-up window that appears Paste your SQL query and click Run query.
We encourage you to write your query elsewhere before copying and pasting into this window.If successful, you'll see a confirmation. Depending on your query you'll see one or more highlighted visualization options.
Hovering over each visualization type will tell you why it was, or wasn’t, suitable for your query. For a list of requirements for each visualization, see our table.
Get help
If you're experiencing errors at this point, see if you need to use one of our workarounds. Otherwise, get in touch with our Customer Success team.
To add a widget, hover over your chosen visualization type and click the + button that replaces it.
With your Databases widget now added to your dashboard, you can set a refresh time so it updates regularly. Refresh time options range from 3 minutes to 12 hours, with a default set to 10 minutes.
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 |
|
Gauge |
|
Line Chart |
|
Column Chart |
|
Bar Chart |
|
Leaderboard |
|
Table |
|
Unsupported statements
For security reasons, only SELECT statements are allowed. However, the following SELECT statements and functions are not yet 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.
Example of using CAST to convert a JSONB datatype
To convert a JSONB
data type, use:
SELECT surname, CAST(address as varchar) FROM gecko_keepers WHERE title = 'Dr
One value from the JSONB
can be displayed as text in a table visualization by using this query:
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.
Example of using CAST to convert currencies
To convert a currency to numeric, use:
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.
Tips 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 this to get the month in which an event has happened:
SELECT TO_CHAR((Subscriptions.ended_at AT TIME ZONE 'UTC'), 'YYYY-MM') AS "month"
Instead, you can use a query like this as DATE_TRUNC
preserves the datatype as "date":
DATE_TRUNC ('month',Subscriptions.ended_at AT TIME ZONE 'UTC') AS "month"
Minimum and maximum values
The following table illustrates the minimum and maximum values for integer and serial types:
Data type | Min value | Max value |
Integer | -2147483648 | 2147483647 |
Serial | 1 | 9007199254740991 |
You might experience a loss in precision when using real
, double precision
, numeric
or decimal
types with large numbers or very small negative numbers.
We support Integer values up to 32 bits.
Bigint
types not only consume a lot of storage but also decrease the performance of the database, therefore, you should have a good reason to use it.
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.
Example uses of TRUNC
To round mean_temperature_c
to 2 decimal points, use:
SELECT date_first_shipped_to, TRUNC(mean_temperature_c,2) as Temperature FROM destinations;
For no decimal points, use:
SELECT date_first_shipped_to, TRUNC(gravity) FROM destinations;
Recommended reading
PostgreSQL documentation
Includes manuals for PostgreSQL versions 11.1, 10.6, 9.6, 9.5, and 9.4.
PostgreSQL Tutorial
Contains practical examples to help you learn PosgreSQL quickly.