PostgreSQL SELECT statement

PostgreSQL SELECT statement is used to extract records from one or more tables into PostgreSQL.

In its simplest form, the syntax for the SELECT statement in PostgreSQL

SELECT_id
FROM tabs
[WHERE conds];

The full syntax for the SELECT PostgreSQL statement

SELECT [ ALL | DISTINCT | DISTINCT ON (distinct_expressions_id) ]
expressions_id
FROM tabs
[WHERE conds]
[GROUP BY_id]
[HAVING cond]
[ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS FIRST | NULLS LAST ]]
[LIMIT [ number_rows | ALL]
[OFFSET offset_value [ ROW | ROWS ]]
[FETCH { FIRST | NEXT } [ fetch_rows ] { ROW | ROWS } ONLY]
[FOR { UPDATE | SHARE } OF table [ NOWAIT ]];

Parameters and arguments of the statement

• ALL – Optional. Returns all matching strings.
• DISTINCT – Optional. Removes duplicates from the result set. Learn more about the DISTINCT operator.
• DISTINCT ON – Optional. Removes duplicates based on different_expressions. Learn more about the DISTINCT ON operator.
• expressions_id – The table or calculations you want to get. Use * if you want to select all columns.
• tabs – The tables from which you want to get the records. At least one table must be specified in the FROM statement.
• WHERE conds – Optional. The conditions that must be met for the records to be selected.
• GROUP BY_id – Optional. It collects data from several records and groups the results into one or more columns.
• HAVING cond – Optional. It is used in combination with GROUP BY to limit groups of returned rows to only those whose TRUE condition.
• ORDER BY expression_id – Optional. It is used to sort the entries in your resulting set.
• LIMIT – Optional. If LIMIT is specified, it controls the maximum number of records to extract. The maximum number of records specified by the number_rows will be returned in the resulting set. The first line returned by LIMIT will be defined as offset_value.
• FETCH – Optional. If FETCH is specified, it controls the maximum number of records to extract. At most, the maximum number of records specified by fetch_rows will be returned in the resulting set. The first line returned by FETCH will be defined as offset_value.
• FOR UPDATE – Optional. Records affected by the query are blocked from being written until the transaction is completed.
• FOR SHARE – Optional. Records affected by a query may be used by other transactions, but cannot be updated or deleted by those other transactions.

Example: select all fields from one table

Let’s see how to use PostgreSQL query SELECT to select all fields in a table.

SELECT *
FROM categories
WHERE category_id >= 2500
ORDER BY category_id ASC;

In this example of the SELECT PostgreSQL statement, we used * to specify that we want to select all fields from the category table where category_id is greater than or equal to 2500. The resulting set is sorted by category_id in ascending order.

Example: select individual fields from one table

You can also use PostgreSQL statement SELECT to select individual fields from a table, unlike all fields from a table.
For example:

SELECT category_id, category_name, comments
FROM categories
WHERE category_name = 'Hardware'
ORDER BY category_name ASC, comments DESC;

This PostgreSQL example SELECT will only return the category_id, category_name, and comments fields from the category table, where category_name is ‘Hardware’. The results are sorted by category_name in ascending order and then by comments in descending order.

Example: selecting fields from several tables

You can also use the SELECT operator of PostgreSQL to extract fields from multiple tables.

SELECT products.product_name, categories.category_name
FROM categories
INNER JOIN products
ON categories.category_id = products.category_id
ORDER BY product_name;

This PostgreSQL SELECT example connects two tables to get the resulting set, which displays the product_name and category_name fields where the category_id value matches in both the categories and product tables. The results are sorted by product_name in ascending order.

PostgreSQL: Select From | Course