Understanding PostgreSQL SELECT
You can use the SELECT statement in PostgreSQL and TimescaleDB to retrieve data from zero or more tables in your database. It returns these results in a result table, called a result set, that consists of zero or more rows.
The SELECT statement is one of the simplest and most complex SQL statements because you can use a lot of optional clauses and keywords with it. This complexity provides greater flexibility when you fetch data from a database.
SELECT syntax:
Simple:
SELECT <expressions>
FROM <tables>;Full:
SELECT [ ALL | DISTINCT | DISTINCT ON (<distinct_expressions>) ]
<expressions>
FROM <tables>
[WHERE <conditions>]
[GROUP BY <expressions>]
[HAVING <condition>]
[ORDER BY <expression> [ ASC | DESC | USING <operator> ] [ NULLS FIRST FIRST | NULLS LASTLAST ]]
[LIMIT [ <number_rows> | ALL]
[OFFSET OFFSET <offset_value> [ ROW | ROWS ]]
[FETCH FETCH { FIRST FIRST | NEXT } [ <fetch_rows> ] { ROW | ROWS } ONLY]
[FOR { UPDATE | SHARE } OF <table> [ NOWAIT ]];
Examples
This example uses a table called stock_data, that contains data about the price of a stock over time:
id | time | group | price |
1 | 2022-06-04 | b | 251.12 |
3 | 2022-06-04 | a | 208.88 |
6 | 2022-06-04 | c | 82.70 |
2 | 2022-06-05 | a | 210.88 |
4 | 2022-06-05 | b | 362.67 |
5 | 2022-06-05 | b | 128.45 |
Using PostgreSQL SELECT to Query a Table
The simplest SELECT query is to use an asterisk without a WHERE clause. This will return every column from every record in the table. For example, this query returns every record from the example table:
SELECT * FROM stock_data;
While SELECT * might work for ad-hoc queries on small tables to eyeball the data, it shouldn’t be used in production code. There, you should select only the data you need for performance and maintainability.
Using PostgreSQL SELECT to Select Expressions
You can also select the results of an expression without including a table.
SELECT 6 * 3 AS result;
Results:
result |
18 |
Using PostgreSQL SELECT to Query Data From a Single Column
If you want all the data from a single column, specify the name of the column in the SELECT statement. For example, this query that retrieves all the data in the price column prices from the stock_data table:
SELECT price FROM stock_data;
Results:
price |
251.12 |
208.88 |
82.70 |
210.88 |
362.67 |
128.45 |
Using PostgreSQL SELECT to Query Data From Multiple Columns
If you only want the data from specific columns in a table, you can specify the names of the columns in the SELECT statement, separated by commas. For example:
SELECT time, price FROM stock_data;
Results:
time | price |
2022-06-04 | 251.12 |
2022-06-04 | 208.88 |
2022-06-04 | 82.70 |
2022-06-05 | 210.88 |
2022-06-05 | 362.67 |
2022-06-05 | 128.45 |
Using Expressions to Modify PostgreSQL SELECT Results
You can modify the results of your SELECT statement by using an expression.
This example combines two of the columns in the example table into one. You can use the || operator to concatenate values in SQL. When you do this, you can add an alias to the generated column to make it easier to identify it in the results. For example, you can use a AS clause to display the result as time_price.
SELECT time || ':' || price AS time_price FROM stock_data;
Results:
time_price |
2022-06-04:251.12 |
2022-06-04:208.88 |
2022-06-04:82.70 |
2022-06-05:210.88 |
2022-06-05:362.67 |
2022-06-05:128.45 |
Using WHERE to Filter PostgreSQL SELECT Queries
You can filter the records by adding a WHERE clause with conditions. This example query returns time and group from the stock_data table, if where the price is more than 200.
SELECT time, group, price from stock_data WHERE price > 200;
Results:
time | group | price |
2022-06-04 | b | 251.12 |
2022-06-04 | a | 208.88 |
2022-06-05 | a | 210.88 |
2022-06-05 | b | 362.67 |
Using PostgreSQL ORDER BY to Change the Order of Results
When you retrieve data from a database, you can use ORDER BY to change the order of the results with the ORDER BY keyword. Here is an example:
SELECT * FROM stock_data ORDER BY price;
Result:
id | time | group | price |
6 | 2022-06-04 | c | 82.70 |
5 | 2022-06-05 | b | 128.45 |
3 | 2022-06-04 | a | 208.88 |
2 | 2022-06-05 | a | 210.88 |
1 | 2022-06-04 | b | 251.12 |
4 | 2022-06-05 | b | 362.67 |
Using PostgreSQL SELECT DISTINCT to Remove Duplicates
If you don’t want duplicates in your results, you can add the DISTINCT keyword to your SELECT clause. For example, this query only retrieves the distinct groups from the stock_data table, and removes the duplicates.
SELECT DISTINCT group from stock_data;
Result:
Group |
b |
a |
c |
Using PostgreSQL SELECT With Calculated Values
This section uses an example table called conditions that contains information about temperature and humidity gathered from different devices in different locations:
time | device_id | temperature | humidity |
2016-11-15 07:00:00 | weather-pro-000000 | 32.4 | 49.8 |
2016-11-15 07:00:00 | weather-pro-000001 | 34.6 | 50.1 |
2016-11-15 07:00:00 | weather-pro-000002 | 73.8 | 74 |
2016-11-15 07:00:00 | weather-pro-000003 | 68.7 | 68 |
To calculate the average temperature of each device in the table, you can use a query like this:
SELECT device_id, AVG(temperature) FROM conditions GROUP BY device_id;
The GROUP BY keyword groups the results by the device_id, and the AVG() function returns the average of each device’s temperature. The results of that query look a bit like this:
Results:
device_id | avg |
weather-pro-000000 | 39.3281 |
weather-pro-000001 | 38.4629 |
weather-pro-000002 | 80.3776 |
weather-pro-000003 | 68.4865 |
You can also find the highest humidity recorded by each device using the MAX() function. For example:
SELECT device_id, MAX(humidity) FROM conditions GROUP BY device_id;
The results of the query look like this:
device_id | avg |
weather-pro-000000 | 55.5 |
weather-pro-000001 | 56 |
weather-pro-000002 | 55.2 |
weather-pro-000003 | 54.4 |
Further Reading
For more information on the SELECT statement and how to use it with PostgreSQL, check out the PostgreSQL documentation on SELECT. For more examples of how you can use SELECT in your SQL queries, see these Timescale documentation sections: