One of the less-known behaviour (I'm reluctant to say "features") is that you can have some sort of virtual field in a table, that will execute a function when the field is accessed. This is due to the logic that PostgreSQL treats `row.property` the same as `property(row)`.<p>For example, we can have the function `full_name(person)` that returns the concatenation of `person.first_name` and `person.last_name`, and we can do `SELECT p.full_name FROM person p`. I think it's pretty neat.
One feature that's incredibly useful, but not on this list, is the JSON support[0]. Being able to store schemaless documents alongside your structured data is very handy in some instances. Being able to query the JSON documents in a useful manner is also incredible.<p>[0] <a href="https://www.postgresql.org/docs/10/static/datatype-json.html" rel="nofollow">https://www.postgresql.org/docs/10/static/datatype-json.html</a>
Posts like this always make a little sad, because at work, we use Microsoft SQL Server[0], and for private programming, I usually prefer SQLite. Still, in some ways, Postgres feels like somebody invented a time machine and brought back a database engine from the future. <i>Sigh</i> Maybe I will find a good reason to use Postgres one of these days... ;-)<p>[0] Do not get me wrong - if you can ignore the price tag, MSSQL is a great database engine. It has never given me any trouble I have not asked for, and it has some very nice features of its own (e.g. the Service Broker).
Table inheritance has some serious limitations regarding foreign keys and some other aspects. The following warning is in the official documentation in that chapter:<p>> These deficiencies will probably be fixed in some future release, but in the meantime considerable care is needed in deciding whether inheritance is useful for your application.
Seeing this has made me realize that I have one HUGE bug with native Postgres full text search.<p>On my blog, I have a lot of articles about the game series Borderlands. If you type "Borderlands" into the search box, it will find them, but if you type "border lands", it won't. Same with "starcraft" and "star craft", etc.<p>It looks like I will have to implement trigrams on top of full text search to fix:<p><a href="https://www.postgresql.org/docs/current/static/pgtrgm.html" rel="nofollow">https://www.postgresql.org/docs/current/static/pgtrgm.html</a>
Table inheritance is a non-starter for the stated use-case. Just don't. If memory serves me well even the Postgres docs recommended against using it for that last I read them. The only sane use-case of inheritance I'm aware of is when you're partitioning a table.
Another feature that should probably be on that list is Common Table Expressions: <a href="https://momjian.us/main/writings/pgsql/cte.pdf" rel="nofollow">https://momjian.us/main/writings/pgsql/cte.pdf</a><p>(I still frequently run into people who work a lot with SQL that don't use CTEs, though less than before :)
Some others more or less obscure features I used and liked:<p>- data checksuming (can be enabled in initdb)<p>- extending the database in C (mostly adding various functions, that would be hard and slow to implement in SQL, but I've also been able to write functions that generate datasets on the fly that are loaded from a custom server over a unix socket)<p>- writing ECPG clients <a href="https://www.postgresql.org/docs/current/static/ecpg.html" rel="nofollow">https://www.postgresql.org/docs/current/static/ecpg.html</a>
FDW is awesome and surprisingly useful. I really want Postgres 10's partitioning support to land in an AWS Redshift PG-reboot someday. Partitioning support is very manual and clunky in that old fork.
Anybody who's used the array types care to share their experience? It's something that caught my eye reading the docs, but wasn't sure how it holds up in actual use.
Table inheritance seems like a natural one for devs working with a lot of class oriented languages. But also, composition over inheritance seems to be a more frequent watchword. Any experiences using this one?<p>Also, triggers seem pretty controversial in the discussions I've been privy too, most devs seem to hate them. Any positive experiences with them?
Site is down, mirror:<p><a href="https://webcache.googleusercontent.com/search?q=cache:kQC5xWrxqF4J:https://pgdash.io/blog/postgres-features.html?h&num=1&hl=en&gl=us&strip=1&vwsrc=0" rel="nofollow">https://webcache.googleusercontent.com/search?q=cache:kQC5xW...</a>
A lot of these things are neat. However, they are sharp tools with specific uses that you should be careful with and understand well before employing them.