TL;DR InfluxDB Tech Tips – Query vs Writing Booleans & Checking Field Types

In this post we recap the week’s most interesting InfluxDB and TICK-stack related issues, workarounds, how-tos with writing booleans and Q&A from GitHub, IRC and the InfluxDB Google Group that you might have missed.

Querying data in a non-DEFAULT retention policy

Q: I created a new retention policy called `secret_garden`. I started writing data to that retention policy and the logs show that the writes are successful, but when I query the data I get an empty response. What am I doing wrong?

Results of the `SHOW RETENTION POLICIES` query:

> SHOW RETENTION POLICIES ON "lands"
name	       duration	  shardGroupDuration   replicaN   DEFAULT
default	       0          168h0m0s             1          true
secret_garden  24h0m0s	  1h0m0s               1          false

Results of a `SELECT` query:

> SELECT * FROM "foliage"
>

A: The retention policy `secret_garden` is not the `DEFAULT` retention policy for your database (see the fifth column in the `SHOW RETENTION POLICIES` output).

You’ll need to fully qualify the measurement `foliage` if you’re querying data in a retention policy that is not the `DEFAULT` retention policy. Fully qualify a measurement by specifying the database and retention policy in the `FROM` clause: `<“database”>.<“retention_policy”>.<“measurement”>`.

Example:

> SELECT * FROM "lands"."secret_garden"."foliage"
name: foliage
-------------
time                             roses
2016-05-31T17:09:04.697144667Z   3
2016-05-31T17:09:07.096708707Z   5

Checking field types

Q: Is there a way to see a field’s data type?

A: Starting with InfluxDB version 1.0, the `SHOW FIELD KEYS` query also returns the field type.

Example:

> SHOW FIELD KEYS FROM all_the_types
name: all_the_types
-------------------
fieldKey  fieldType
blue      string
green     boolean
orange    integer
yellow    float

Querying vs. writing booleans

Q: I’m able to successfully write booleans to my database, but I can’t seem to use them in my query’s `WHERE` clause.

> INSERT hamlet tobe=t
> INSERT hamlet tobe=f
> SELECT * FROM "hamlet" WHERE "tobe"=t
>

A: Acceptable boolean syntax differs for writes and for queries.

In your case, InfluxDB understands that `t` means true when you write your data but it doesn’t understand that `t` means true when you query your data. You’ll need to use `true`, `True`, or `TRUE` to reference that boolean value in a query.

Example:

> SELECT * FROM "hamlet" WHERE "tobe"=true
name: hamlet
------------
time                             tobe
2016-05-31T18:07:32.93151244Z    true

For more InfluxDB tips, check out our Frequently Encountered Issues page and feel free to post your questions in the InfluxDB users group.

What’s next?

• Download and get started with InfluxDB v0.13
• Schedule a FREE 20 minute consultation with a Solutions Architect to review your InfluxDB project
• Attend one of our FREE virtual training seminars.
• Looking for InfluxDB Clustering on your infrastructure? Contact Sales to get a demo of InfluxDB Enterprise plus pricing information.