Running your SQL query in Databases

In this article:

Once you've connected to your SQL database, it's time to write your query and paste it into Geckoboard.

Notes

  • 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.

To run your SQL query in Databases follow these steps:

  1. 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. Enter your SQL query in the pop-up window
  2. If successful, you'll see a confirmation. Depending on your query you'll see one or more highlighted visualization options. Highlighted visualization types for your SQL queryHovering over each visualization type will tell you why it was, or wasn’t, suitable for your query.Number visualization eligibility

    Get help

    If you're experiencing errors at this point, see if you need to use one of our workarounds. Otherwise, drop us a line and our Customer Success team will help you.

    Note

    When creating a line chart, the first column is the x axis and must be a time stamp.

  3. To add a widget, hover over your chosen visualization type and click the + button that replaces it.
  4. And that's it! Your new widget will now appear on your dashboard.

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
  • LAST_DAY()
  • UNIX_TIMESTAMP()
  • CONVERT_TZ()
  • DATEDIFF()

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.

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:

Data type Min value Max value
Integer -2147483648 2147483647
Serial 1 9007199254740991

Note

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;

Disconnections

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

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.

Was this article helpful?

Awesome! 👍  Thanks so much for your feedback!

Sorry about that! Start a conversation now.