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

By Regan Kuchan / Jun 01, 2016 / Tech Tips

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?