Timescale Logo

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:

Get started with a PostgreSQL—but faster—database optimized for time series

Timescale Logo

Subscribe to the Timescale Newsletter

By submitting, I acknowledge Timescale’s Privacy Policy
2024 © Timescale Inc. All rights reserved.