SELECT

The SELECT clause specifies the list of columns that will be returned by the query. While it appears first in the clause, logically the expressions here are executed only at the end. The SELECT clause can contain arbitrary expressions that transform the output, as well as aggregates and window functions.

Examples

Select all columns from the table called tbl:

Query

SELECT * FROM tbl;

Result

 id | name     | col1 | col2 | some column name | i | a  | b  | c  | number | number1 | number2----+----------+------+------+------------------+---+----+----+----+--------+---------+---------  1 | mark     |    1 |    2 | alpha            | 1 | 10 | 20 | 30 |     10 |     100 |    1000  3 | bookmark |    4 |    5 | beta             | 2 | 11 | 21 | 31 |     20 |     200 |    2000  7 | other    |    9 |   16 | gamma            | 3 | 12 | 22 | 32 |   NULL |     300 |    3000

Perform arithmetic on the columns in a table, and provide an alias:

Query

SELECT col1 + col2 AS res, sqrt(col1) AS root FROM tbl;

Result

 res | root-----+------   3 |    1   9 |    2  25 |    3

Use prefix aliases:

Query

SELECT    res: col1 + col2,    root: sqrt(col1)FROM tbl;

Result

 res | root-----+------   3 |    1   9 |    2  25 |    3

Select all unique cities from the addresses table:

Query

SELECT DISTINCT city FROM addresses;

Result

 city---------- Lakeside Linden Mallard

Return the total number of rows in the addresses table:

Query

SELECT count(*) FROM addresses;

Result

 count-------     6

Select all columns except the city column from the addresses table:

Query

SELECT * EXCLUDE (city) FROM addresses;

Result

 address         | street_name | income | zip        | country | population | number1 | number2-----------------+-------------+--------+------------+---------+------------+---------+--------- 123 Spruce Blvd | Spruce Blvd |  50000 | 11111      | NL      |        100 |       1 |      10 111 Birch Ln    | Birch Ln    |  60000 | 11111      | NL      |        100 |       2 |      20 111 Birch Ln    | Birch Ln    |  70000 | 11111      | US      |         50 |       3 |      30 111 Birch Ln    | Birch Ln    |  80000 | 11111-0001 | US      |         50 |       4 |      40 5 Pond Road     | Pond Road   |  90000 | 22222      | UK      |         25 |       5 |      50 9 Lake View     | Lake View   |  40000 | 33333      | UK      |         25 |       6 |      60

Select all columns from the addresses table, but replace city with lower(city):

Query

SELECT * REPLACE (lower(city) AS city) FROM addresses;

Result

 address         | city     | street_name | income | zip        | country | population | number1 | number2-----------------+----------+-------------+--------+------------+---------+------------+---------+--------- 123 Spruce Blvd | linden   | Spruce Blvd |  50000 | 11111      | NL      |        100 |       1 |      10 111 Birch Ln    | linden   | Birch Ln    |  60000 | 11111      | NL      |        100 |       2 |      20 111 Birch Ln    | lakeside | Birch Ln    |  70000 | 11111      | US      |         50 |       3 |      30 111 Birch Ln    | lakeside | Birch Ln    |  80000 | 11111-0001 | US      |         50 |       4 |      40 5 Pond Road     | mallard  | Pond Road   |  90000 | 22222      | UK      |         25 |       5 |      50 9 Lake View     | mallard  | Lake View   |  40000 | 33333      | UK      |         25 |       6 |      60

Select all columns matching the given regular expression from the table:

Query

SELECT COLUMNS('number[0-9]+') FROM addresses;

Result

 number1 | number2---------+---------       1 |      10       2 |      20       3 |      30       4 |      40       5 |      50       6 |      60

Compute a function on all given columns of a table:

Query

SELECT min(COLUMNS(*)) FROM addresses;

Result

 address      | city     | street_name | income | zip   | country | population | number1 | number2--------------+----------+-------------+--------+-------+---------+------------+---------+--------- 111 Birch Ln | Lakeside | Birch Ln    |  40000 | 11111 | NL      |         25 |       1 |      10

To select columns with spaces or special characters, use double quotes ("):

Query

SELECT "Some Column Name" FROM tbl;

Result

 some column name------------------ alpha beta gamma

Syntax

SELECT List

The SELECT clause contains a list of expressions that specify the result of a query. The select list can refer to any columns in the FROM clause, and combine them using expressions. As the output of a SQL query is a table – every expression in the SELECT clause also has a name. The expressions can be explicitly named using the AS clause (e.g., expr AS name). If a name is not provided by the user the expressions are named automatically by the system.

Tip

Column names are case-insensitive. See the Rules for Case Sensitivity for more details.

Star Expressions

Select all columns from the table called tbl:

Query

SELECT *FROM tbl;

Result

 id | name     | col1 | col2 | some column name | i | a  | b  | c  | number | number1 | number2----+----------+------+------+------------------+---+----+----+----+--------+---------+---------  1 | mark     |    1 |    2 | alpha            | 1 | 10 | 20 | 30 |     10 |     100 |    1000  3 | bookmark |    4 |    5 | beta             | 2 | 11 | 21 | 31 |     20 |     200 |    2000  7 | other    |    9 |   16 | gamma            | 3 | 12 | 22 | 32 |   NULL |     300 |    3000

Select all columns matching the given regular expression from the table:

Query

SELECT COLUMNS('number[0-9]+')FROM addresses;

Result

 number1 | number2---------+---------       1 |      10       2 |      20       3 |      30       4 |      40       5 |      50       6 |      60

The star expression is a special expression that expands to multiple expressions based on the contents of the FROM clause. In the simplest case, * expands to all expressions in the FROM clause. Columns can also be selected using regular expressions or lambda functions. See the star expression page for more details.

DISTINCT Clause

Select all unique cities from the addresses table:

Query

SELECT DISTINCT cityFROM addresses;

Result

 city---------- Lakeside Linden Mallard

The DISTINCT clause can be used to return only the unique rows in the result – so that any duplicate rows are filtered out.

Tip

Queries starting with SELECT DISTINCT run deduplication, which is an expensive operation. Therefore, only use DISTINCT if necessary.

DISTINCT ON Clause

Select only the highest population city for each country:

Query

SELECT DISTINCT ON(country) city, populationFROM citiesORDER BY population DESC;

Result

 city      | population-----------+------------ New York  |    8804190 London    |    8799800 Amsterdam |     921402

The DISTINCT ON clause returns only one row per unique value in the set of expressions as defined in the ON clause. If an ORDER BY clause is present, the row that is returned is the first row that is encountered as per the ORDER BY criteria. If an ORDER BY clause is not present, the first row that is encountered is not defined and can be any row in the table.

Tip

When querying large datasets, using DISTINCT on all columns can be expensive. Therefore, consider using DISTINCT ON on a column (or a set of columns) which guarantees a sufficient degree of uniqueness for your results. For example, using DISTINCT ON on the key column(s) of a table guarantees full uniqueness.

Aggregates

Return the total number of rows in the addresses table:

Query

SELECT count(*)FROM addresses;

Result

 count-------     6

Return the total number of rows in the addresses table grouped by city:

Query

SELECT city, count(*)FROM addressesGROUP BY city;

Result

 city     | count----------+------- Lakeside |     2 Linden   |     2 Mallard  |     2

Aggregate functions are special functions that combine multiple rows into a single value. When aggregate functions are present in the SELECT clause, the query is turned into an aggregate query. In an aggregate query, all expressions must either be part of an aggregate function, or part of a group (as specified by the GROUP BY clause).

Window Functions

Generate a row_number column containing incremental identifiers for each row:

Query

SELECT row_number() OVER ()FROM sales;

Result

 row_number------------          1          2

Compute the difference between the current amount, and the previous amount, by order of time:

Query

SELECT amount - lag(amount) OVER (ORDER BY time)FROM sales;

Result

 ?column?----------     NULL      100

Window functions are special functions that allow the computation of values relative to other rows in a result. Window functions are marked by the OVER clause which contains the window specification. The window specification defines the frame or context in which the window function is computed. See the window functions page for more information.

unnest Function

Unnest an array by one level:

Query

SELECT unnest([1, 2, 3]);

Result

 unnest--------      1      2      3

Unnest a struct by one level:

Query

SELECT unnest({'a': 42, 'b': 84});

Result

 a  | b----+---- 42 | 84

The unnest function is a special function that can be used together with arrays, lists, or structs. The unnest function strips one level of nesting from the type. For example, INTEGER[] is transformed into INTEGER. STRUCT(a INTEGER, b INTEGER) is transformed into a INTEGER, b INTEGER. The unnest function can be used to transform nested types into regular scalar types, which makes them easier to operate on.