SELECT
4 methodsAdvanced SELECT constructs supported by PostgreSQL.
SELECT col, agg() OVER (PARTITION BY ... ORDER BY ...) FROM tblComputes a value over a sliding window of rows related to the current row, without collapsing rows.
Parameters
| Name | Type | Description |
|---|---|---|
| PARTITION BY | column-list | Divides rows into partitions for the window. |
| ORDER BY | column-list | Ordering inside each partition. |
Returns
ResultSet
Example
postgresql
SELECT user_id, created_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
FROM events;WITH name AS (SELECT ...) SELECT ... FROM nameCommon Table Expression (CTE) that names a subquery and references it in the outer SELECT.
Parameters
| Name | Type | Description |
|---|---|---|
| name | identifier | CTE name. |
Returns
ResultSet
Example
postgresql
WITH active AS (
SELECT id FROM users WHERE active
)
SELECT o.* FROM orders o JOIN active a ON a.id = o.user_id;SELECT ... FROM a, LATERAL (SELECT ... WHERE a.id = ref) bLATERAL join allows a subquery in the FROM clause to reference columns of preceding FROM items.
Parameters
| Name | Type | Description |
|---|---|---|
| LATERAL | keyword | Enables cross-references to earlier FROM items. |
Returns
ResultSet
Example
postgresql
SELECT u.id, latest.id AS last_order
FROM users u
LEFT JOIN LATERAL (
SELECT id FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1
) latest ON true;INSERT|UPDATE|DELETE ... RETURNING colsReturns rows affected by an INSERT, UPDATE or DELETE, useful for reading generated keys and triggers.
Parameters
| Name | Type | Description |
|---|---|---|
| cols | column-list | Columns to return; * returns all. |
Returns
ResultSet
Example
postgresql
INSERT INTO users (email) VALUES ('[email protected]')
RETURNING id, created_at;