Published at Aug 15, 2024
Written by Team Timescale
In PostgreSQL or TimescaleDB, the FROM clause is fundamental to querying data. It specifies the PostgreSQL table from which to retrieve or delete data, serving as the starting point for your queries. It is a critical part of any SELECT, DELETE, or UPDATE statement.
The FROM clause can also be used to join multiple tables, allowing you to combine data from different sources.
The basic syntax of the FROM clause is as follows:
SELECT column1, column2, ...
FROM table_name;
When joining multiple tables, the syntax is:
SELECT column1, column2, ...
FROM table1
JOIN table2 ON table1.column = table2.column;
Let's consider a table iot_data with the following structure and data:
CREATE TABLE iot_data (
id SERIAL PRIMARY KEY,
device_id VARCHAR(50),
temperature NUMERIC,
humidity NUMERIC,
recorded_at TIMESTAMP
);
INSERT INTO iot_data (device_id, temperature, humidity, recorded_at) VALUES
('device_1', 22.5, 55.0, '2023-01-15 08:00:00'),
('device_2', 21.0, 60.0, '2023-01-15 08:05:00'),
('device_1', 23.0, 57.0, '2023-01-15 08:10:00'),
('device_2', 19.5, 62.0, '2023-01-15 08:15:00'),
('device_1', 20.0, 59.0, '2023-01-15 08:20:00'),
('device_3', 24.0, 54.0, '2023-01-15 08:25:00'),
('device_2', 18.5, 63.0, '2023-01-15 08:30:00'),
('device_3', 22.0, 55.5, '2023-01-15 08:35:00'),
('device_1', 21.5, 58.0, '2023-01-15 08:40:00'),
('device_3', 23.5, 53.0, '2023-01-15 08:45:00');
Let's dive into some examples to see the FROM clause in action.
Example 1: Selecting data from a single table
Suppose we want to retrieve all the data from the iot_data table. We can use the FROM clause to achieve this:
SELECT id, device_id, temperature, humidity, recorded_at
FROM iot_data;
Result:
id | device_id | temperature | humidity | recorded_at
----+-----------+-------------+----------+---------------------
1 | device_1 | 22.5 | 55.0 | 2023-01-15 08:00:00
2 | device_2 | 21.0 | 60.0 | 2023-01-15 08:05:00
3 | device_1 | 23.0 | 57.0 | 2023-01-15 08:10:00
4 | device_2 | 19.5 | 62.0 | 2023-01-15 08:15:00
5 | device_1 | 20.0 | 59.0 | 2023-01-15 08:20:00
6 | device_3 | 24.0 | 54.0 | 2023-01-15 08:25:00
7 | device_2 | 18.5 | 63.0 | 2023-01-15 08:30:00
8 | device_3 | 22.0 | 55.5 | 2023-01-15 08:35:00
9 | device_1 | 21.5 | 58.0 | 2023-01-15 08:40:00
10 | device_3 | 23.5 | 53.0 | 2023-01-15 08:45:00
This query retrieves all columns from the iot_data table.
Example 2: Filtering data with WHERE
To retrieve data for a specific device, we can combine the FROM clause with a WHERE clause:
SELECT id, device_id, temperature, humidity, recorded_at
FROM iot_data
WHERE device_id = 'device_1';
Result:
id | device_id | temperature | humidity | recorded_at
----+-----------+-------------+----------+---------------------
1 | device_1 | 22.5 | 55.0 | 2023-01-15 08:00:00
3 | device_1 | 23.0 | 57.0 | 2023-01-15 08:10:00
5 | device_1 | 20.0 | 59.0 | 2023-01-15 08:20:00
9 | device_1 | 21.5 | 58.0 | 2023-01-15 08:40:00
This query retrieves rows where the device_id is 'device_1'.
Example 3: Joining multiple tables
Suppose we have another table device_info that contains additional information about the devices:
CREATE TABLE device_info (
device_id VARCHAR(50) PRIMARY KEY,
device_name VARCHAR(100),
location VARCHAR(100)
);
INSERT INTO device_info (device_id, device_name, location) VALUES
('device_1', 'Temperature Sensor 1', 'Building A'),
('device_2', 'Temperature Sensor 2', 'Building B'),
('device_3', 'Temperature Sensor 3', 'Building C');
We can join iot_data with device_info to get the location and name of each device along with their recorded data:
SELECT iot_data.id, iot_data.device_id, device_info.device_name, device_info.location, iot_data.temperature, iot_data.humidity, iot_data.recorded_at
FROM iot_data
JOIN device_info ON iot_data.device_id = device_info.device_id;
Result:
id | device_id | device_name | location | temperature | humidity | recorded_at
----+-----------+--------------------+--------------+-------------+----------+---------------------
1 | device_1 | Temperature Sensor 1 | Building A | 22.5 | 55.0 | 2023-01-15 08:00:00
2 | device_2 | Temperature Sensor 2 | Building B | 21.0 | 60.0 | 2023-01-15 08:05:00
3 | device_1 | Temperature Sensor 1 | Building A | 23.0 | 57.0 | 2023-01-15 08:10:00
4 | device_2 | Temperature Sensor 2 | Building B | 19.5 | 62.0 | 2023-01-15 08:15:00
5 | device_1 | Temperature Sensor 1 | Building A | 20.0 | 59.0 | 2023-01-15 08:20:00
6 | device_3 | Temperature Sensor 3 | Building C | 24.0 | 54.0 | 2023-01-15 08:25:00
7 | device_2 | Temperature Sensor 2 | Building B | 18.5 | 63.0 | 2023-01-15 08:30:00
8 | device_3 | Temperature Sensor 3 | Building C | 22.0 | 55.5 | 2023-01-15 08:35:00
9 | device_1 | Temperature Sensor 1 | Building A | 21.5 | 58.0 | 2023-01-15 08:40:00
10 | device_3 | Temperature Sensor 3 | Building C | 23.5 | 53.0 | 2023-01-15 08:45:00
This query retrieves data from both iot_data and device_info tables, providing a complete picture of each device's data along with its location and name.
The FROM clause is a fundamental part of SQL and PostgreSQL, specifying the source tables for your queries. To learn more about other foundational PostgreSQL concepts, check out our Postgres basics section. For more advanced guides, visit Postgres best practices.