Lesser Known Postgres Features (2021)

I'd probably love to have the "REFRESH ON COMMIT"-feature for materialized views, to refresh (only the) impacted rows automatically whenever rows of the source table(s) change :)

(the same like in Oracle - https://docs.oracle.com/en/database/oracle/oracle-database/1... - but I would not need the fancy "query rewrite" feature that make SQLs use the matView even if they don't explicitly mention it in their SQL)

Discussed at the time, 166 comments

https://news.ycombinator.com/item?id=29163319

One of the features I wish Postgres had is "column matchers" from ClickHouse: https://clickhouse.com/docs/en/sql-reference/statements/sele...

    SELECT COLUMNS('_id$') FROM table;
    SELECT * EXCEPT(id) FROM table;
    SELECT * REPLACE(i + 1 AS i) FROM table;
    SELECT * APPLY(toString) FROM table;

Some overlap, but my similar post mentions a few other things too, with a lot of links to sources to learn more :)

https://medium.com/cognite/postgres-can-do-that-f221a8046e

I discovered `\crosstabview` the other day! It does, of course, use crosstab underneath. It's just some psql magic.

One tiny thing I discovered which I use a surprising amount is `table <tablename>;` is equivalent to `select * from <tablename>;`.

I bet that the `watch` feature would make to the list if the article was written these days.

`watch` is the coolest Postgres feature that I've discovered recently.

There are a few nice things in that, like (x, y) IN (SELECT ...), ROW_NUMBER() OVER (PARTITION BY ...), and (start1, end1) OVERLAPS (start2, end2).

> With PostgreSQL 14 released just a few weeks ago...

Would benefit from putting "2021" in the title