Available Tables

TagoSQL exposes your data through virtual table functions. Every table in a FROM clause must use one of them (plain table names are rejected) and must have an alias. Pick by what you want back:

You want	Use	Example
The sensor readings stored on one device	device('id')	FROM device('62a1...') AS d
The rows stored in one entity	entity('id')	FROM entity('62a1...') AS e
The latest reading per device, many devices	device_data_by_tag('key','value')	FROM device_data_by_tag('type','sensor') AS f
Your device inventory (names, tags, status)	devices()	FROM devices() AS d
Your entity inventory	entities()	FROM entities() AS e

device and entity also have a tag form (device_tag('key','value'), entity_tag('key','value')) that picks the first resource matching that tag instead of naming an id. When multiple resources match, which one you get is not guaranteed; use the id form when you need a specific one, and device_data_by_tag when you want all matching devices.

Data from one device

device('id') and device_tag('key','value') read the time-series data stored on a device.

Column	Type
id	string
variable	string
value	auto (number / string / boolean)
unit	string
group	string
lat	number
lng	number
metadata	json
time	timestamp
created_at	timestamp

The value column stores numbers, strings, and booleans. In a WHERE clause its type follows what you compare it against: value > 30 treats it as a number, value = 'open' as a string, value = true as a boolean. In the output, value comes back as the type it was stored with.

Data from one entity

entity('id') and entity_tag('key','value') read the rows stored in one of your Entities. Columns are dynamic: each entity exposes exactly the fields defined in its own schema, with their native types. SELECT * returns that entity's columns, and you can only reference columns the entity actually has.

Data from many devices

device_data_by_tag('key','value', ...) is the fleet function: one query returns the latest reading per device across every active device carrying ALL the tags you list (1 to 5 pairs, AND-combined). Built for dashboards that show a whole fleet at once, like a map with each device's last location or a table with each device's last temperature.

SELECT device, device_name, value, time
FROM device_data_by_tag('org_id', 'XY', 'type', 'sensor') AS f
WHERE variable = 'temperature' AND time > $1
ORDER BY device

It exposes the same columns as data from one device, plus two extras: device (the device id) and device_name.

A few rules keep it fast even against hundreds of devices; breaking one is a clear 400:

• Filter on one variable (variable = '...') and give a recent time bound (time > <timestamp>), both as plain AND conditions. How far back the bound may reach depends on your plan (see Limits).
• The tag filter may match at most your plan's device cap per request. For larger fleets, page with the after_device body field (see Executing Queries).
• No aggregates or grouping, and no JOINs with other tables. Devices that never stored data are skipped.

Your device inventory

devices() and devices_tag('key','value') list your devices themselves (not their data). Useful for reports over the fleet: how many devices are active, which ones stopped sending data, which carry a tag.

Column	Type
id	string
name	string
description	string
active	boolean
visible	boolean
type	string
tags	json
network	string
connector	string
last_input	timestamp
created_at	timestamp
updated_at	timestamp
chunk_retention	string
chunk_period	string

Your entity inventory

entities() and entities_tag('key','value') list your entities.

Column	Type
id	string
name	string
tags	json
created_at	timestamp
updated_at	timestamp

Inventory tables are single-table-only: they cannot be joined with any other table.

What SQL is supported

Allowed: SELECT (with aliases, DISTINCT, *), the aggregates COUNT/AVG/SUM/MIN/MAX, JOIN/LEFT JOIN/RIGHT JOIN with ON (Starter plan or above), WHERE with the usual comparison and logical operators, IN, BETWEEN, IS NULL, LIKE/ILIKE, GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET, and arithmetic expressions such as value * 1.8 + 32.

Not allowed: subqueries, CTEs (WITH), UNION, window functions, type casts (::type), any write operation, and database system functions.

Example: correlate two devices by time with a JOIN.

SELECT a.value AS sensor_value, b.value AS actuator_value
FROM device('SENSOR_ID') AS a
JOIN device('ACTUATOR_ID') AS b ON a.time = b.time
WHERE a.variable = 'temperature'
ORDER BY a.time DESC
LIMIT 50

Discovering your schema

You do not need to memorize any of this. The schema discovery endpoint (GET /sql/tables, see the TagoIO API reference) returns the full catalog above plus the list of your own devices and entities, and can resolve the columns of a specific entity. Query editors use it for autocomplete and click-to-insert.

Data output usage

Every query execution that reaches the database counts the rows it returns against your profile's data output allocation, the same allocation regular data reads use. Results served from a query's cache are free: they consume no output. See Limits for how the caps and metering work.