Lesser Known Postgres Features (2021) (hakibenita.com)

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

Would benefit from putting "2021" in the title

Discussed at the time, 166 comments

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

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)

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

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;

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

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.