Unlock PostgreSQL’s Powerful SQL Features: CTEs, UPSERT, Sampling, and More

1. WITH Queries (CTE)

PostgreSQL supports Common Table Expressions (CTE) via the WITH clause. A CTE defines a temporary result set that can be referenced within the same query. Adding RECURSIVE enables hierarchical queries.

WITH t AS (
  SELECT generate_series(1,3)
)
SELECT * FROM t;

Recursive example that walks a parent‑child hierarchy:

WITH RECURSIVE r AS (
  SELECT * FROM test_area WHERE id = 7
  UNION ALL
  SELECT a.* FROM test_area a, r WHERE a.id = r.fatherid
)
SELECT * FROM r ORDER BY id;

2. Batch Insert

Three efficient ways to insert multiple rows in a single statement:

INSERT … SELECT – copies data from another table or query.

INSERT … VALUES – lists several value tuples.

COPY / \COPY – bulk loads data from a file, offering the highest throughput.

INSERT INTO tbl_batch3(id,info) VALUES (1,'a'),(2,'b'),(3,'c');

Verify the rows with a simple SELECT query.

3. RETURNING Clause

The RETURNING clause returns the rows affected by INSERT, UPDATE or DELETE without a separate query.

-- Insert and return the new row
INSERT INTO test_r1(flag) VALUES ('a') RETURNING *;

-- Update and return the updated row
UPDATE test_r1 SET flag='p' WHERE id=1 RETURNING *;

-- Delete and return the deleted row
DELETE FROM test_r1 WHERE id=2 RETURNING *;

4. UPSERT (ON CONFLICT)

PostgreSQL’s upsert syntax INSERT … ON CONFLICT … DO UPDATE resolves unique‑key violations by updating the existing row.

INSERT INTO user_logins(user_name,login_cnt) VALUES ('matiler',1),('francs',1)
ON CONFLICT(user_name) DO UPDATE SET
  login_cnt = user_logins.login_cnt + EXCLUDED.login_cnt,
  last_login_time = now();

After execution the conflicting row’s counter is incremented and its timestamp refreshed.

5. Table Sampling (TABLESAMPLE)

Since PostgreSQL 9.5 the TABLESAMPLE clause allows random sampling of large tables. Two methods are available:

SYSTEM – samples whole data blocks; very fast but less precise at the row level.

BERNOULLI – samples individual rows; statistically more accurate but slower.

Example (0.01 % of rows):

SELECT * FROM test_sample TABLESAMPLE SYSTEM(0.01);

Performance comparison (SYSTEM ≈ 0.166 ms, BERNOULLI ≈ 22.5 ms) is illustrated below.

6. Aggregation Functions

Beyond the standard aggregates, PostgreSQL provides:

string_agg(expression, delimiter) – concatenates values into a delimited string.

array_agg(expression) – aggregates values into an array, enabling array‑based operations.

Examples:

SELECT country, string_agg(city, ',') FROM city GROUP BY country;
SELECT country, array_agg(city) FROM city GROUP BY country;

7. Window Functions

Window functions compute values over a set of rows related to the current row without collapsing the result set. Common functions include row_number(), rank(), dense_rank(), and aggregate functions used with OVER().

function_name([args]) OVER (PARTITION BY expr ORDER BY expr ROWS BETWEEN ...)

Compute the average score per subject while keeping each individual row:

SELECT id, subject, stu_name, score,
       avg(score) OVER (PARTITION BY subject) AS subject_avg
FROM score;

Ranking examples illustrate the difference between rank() (gaps) and dense_rank() (no gaps) when ties occur.