O'Reilly logo

Practical PostgreSQL by John C. Worsley, Joshua D. Drake

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Select

Retrieves rows from a table or view.

Synopsis

SELECT [ ALL | DISTINCT [ ON ( distinct_expression [, ...] ) ] ]
    target_expression [ AS output_name ] [, ...]
    [ FROM from_item [ { , | CROSS JOIN } ...] ]
    [ WHERE condition ]
    [ GROUP BY aggregate_expression [, ...] ]
    [ HAVING aggregate_condition [, ...] ]
    [ { UNION | INTERSECT | EXCEPT [ALL] } select ]
    [ ORDER BY order_expression [ ASC | DESC | USING operator ] [, ...] ]
    [ FOR UPDATE [ OF update_table [, ...] ] ]
    [ LIMIT { ALL | count } [ { OFFSET | , } start ] ]

from_item ::= { [ ONLY ] table_name [ * ]
                  [ [ AS ] from_alias [ ( column_alias_list ) ] ] |
                ( select ) [ [ AS ] alias [ ( column_alias_list ) ] ] |
                from_item [ NATURAL ] join_type from_item
                  [ ON ( join_condition ) | USING ( join_column_list ) ]
              }

join_type ::= [ INNER |
                LEFT  [ OUTER ] |
                RIGHT [ OUTER ] |
                FULL  [ OUTER ]
              ] JOIN

Parameters

ALL | DISTINCT

The DISTINCT keyword indicates that duplicate values found in two or more rows will not be shown after the first row. The ALL keyword explicitly reinforces the default to retrieve all rows regardless of uniqueness.

Note that the ORDER BY clause sorts rows before the DISTINCT clause removes non-unique rows. Use these clauses together to ensure that the row found is the row you intend to retrieve.

DISTINCT ON

The ON keyword, following the DISTINCT keyword, allows you to specify one or more distinct_expressions by which to judge uniqueness.

distinct_expression

A column name within a from_item, or a valid expression, whose value is used ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required