Understanding FROM in PostgreSQL (With Examples)

Light purple geometric shapes over a black background

By Tiger Data team

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.

PostgreSQL FROM Syntax

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;

Example table

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');

Practical examples

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.

Next Steps

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.

Understanding FROM in PostgreSQL (With Examples)

By Tiger Data team

Published at Aug 15, 2024

Written by Team Timescale

The FROM clause can also be used to join multiple tables, allowing you to combine data from different sources.

PostgreSQL FROM Syntax

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;

Example table

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');

Practical examples

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.