---
title: "Understanding PostgreSQL SELECT"
description: "SELECT is one of the simplest and most complex SQL statements—learn how to use in PostgreSQL and TimescaleDB."
section: "Postgres basics"
---

> **TimescaleDB is now Tiger Data.**

You can use the [`SELECT`](https://www.postgresql.org/docs/current/sql-select.html) 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`](https://www.timescale.com/learn/understanding-group-by-in-postgresql-with-examples) 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](https://www.postgresql.org/docs/current/sql-select.html). For more examples of how you can use `SELECT` in your SQL queries, see these Timescale documentation sections:

- [Query your data](https://docs.timescale.com/getting-started/latest/query-data/)
- [SELECT commands](https://docs.timescale.com/timescaledb/latest/how-to-guides/query-data/select/)
- [Advanced analytic queries](https://docs.timescale.com/timescaledb/latest/how-to-guides/query-data/advanced-analytic-queries/)