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>;Copy

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 ]]; Copy

Examples

This example uses a table called stock_data, that contains data about the price of a stock over time:

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; Copy

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; Copy

Results:

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; Copy

Results:

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; Copy

Results:

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; Copy

Results:

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; Copy

Results:

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; Copy

Result:

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; Copy

Result:

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:

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; Copy

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:

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; Copy

The results of the query look like this: 

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:

• Query your data

• SELECT commands

• Advanced analytic queries