SELECT with PostgreSQL-specific features - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the PostgreSQL-specific keyword RETURNING do?

The RETURNING clause in PostgreSQL allows you to return values from rows that are inserted, updated, or deleted, without needing a separate SELECT query. It is often used with INSERT, UPDATE, or DELETE statements to get the affected rows immediately.

intermediate

How does the SELECT DISTINCT ON clause work in PostgreSQL?

SELECT DISTINCT ON (expression) returns the first row for each unique value of the expression. It is useful to get one row per group, unlike standard DISTINCT which removes duplicate rows entirely.

intermediate

What is the purpose of the FILTER clause in aggregate functions in PostgreSQL?

The FILTER clause lets you apply a condition inside an aggregate function to include only rows that meet that condition. For example, COUNT(*) FILTER (WHERE status = 'active') counts only rows where status is 'active'.

advanced

Explain the use of the WITH ORDINALITY clause in PostgreSQL.

WITH ORDINALITY adds a column that numbers the rows returned by a set-returning function or a table expression. This is useful when you want to keep track of the order of rows generated by functions like unnest().

advanced

What does the TABLESAMPLE clause do in a PostgreSQL SELECT query?

TABLESAMPLE allows you to retrieve a random sample of rows from a table. It is useful for testing or analysis when you don't need the full dataset. Different sampling methods like BERNOULLI or SYSTEM can be specified.

What does SELECT DISTINCT ON (column) return in PostgreSQL?

A The first row for each unique value in the specified column

B All rows with duplicates removed from the entire result

C Only rows where the column is not null

D Rows sorted by the specified column

SELECT DISTINCT ON (column) returns the first row for each unique value in that column, unlike standard DISTINCT which removes all duplicate rows.

Which clause allows you to get the affected rows immediately after an INSERT in PostgreSQL?

A WITH ORDINALITY

B RETURNING

C FILTER

D TABLESAMPLE

The RETURNING clause returns rows affected by INSERT, UPDATE, or DELETE statements immediately.

What does the FILTER clause do inside an aggregate function?

A Filters columns to include in aggregation

B Filters the final result set after aggregation

C Filters rows before aggregation based on a condition

D Filters the query plan for optimization

FILTER applies a condition inside an aggregate function to include only rows that meet that condition during aggregation.

What is the purpose of WITH ORDINALITY in PostgreSQL?

A Adds a row number column to rows returned by a set-returning function

B Filters rows based on ordinal values

C Sorts rows in ascending order

D Limits the number of rows returned

WITH ORDINALITY adds a column numbering the rows from set-returning functions, helping track their order.

Which sampling methods can be used with TABLESAMPLE in PostgreSQL?

A RANDOM and SEQUENTIAL

B HASH and RANGE

C FULL and PARTIAL

D BERNOULLI and SYSTEM

TABLESAMPLE supports sampling methods like BERNOULLI and SYSTEM to retrieve random rows.

Describe how you would use the RETURNING clause in PostgreSQL and why it is useful.

Think about how to get data from changed rows without extra queries.

Explain the difference between DISTINCT and DISTINCT ON in PostgreSQL SELECT queries.

Focus on how each handles duplicates and grouping.