Lesser-known Postgres features
hakibenita.com
Lesser-known Postgres features
1–10 of 167 posts
Re: Lesser-known Postgres features
#2On this first point — 'Get the Number of Updated and Inserted Rows in an Upsert' — unfortunately this `xmax` method is not always reliable and there does not seem to be any reliable alternative.
I posted to pgsql-hackers about this problem here: https://www.postgresql.org/message-id/DE57F14C-DB96-4F17-925...
Re: Lesser-known Postgres features
#3Great article, always learn a lot from this author.
Here is another way I have used to do pivot tables / crosstab in postgres where you have a variable number of columns in the output:
https://gist.github.com/ryanguill/101a19fb6ae6dfb26a01396c53...
You can try it out here: https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=5dbbf7eadf0ed...
Re: Lesser-known Postgres features
#4Great article. Read through some of the other articles as well - some high quality content there!
Re: Lesser-known Postgres features
#5I've seen similarly titled articles and they are usually full of the features that aren't used for a reason (MONEY column type, etc).
It's nice to see one that's full of good advice.
Re: Lesser-known Postgres features
#6I love those kinds of posts. Always something that you keep for life.
Re: Lesser-known Postgres features
#7Avoid using lesser known features...
They're the ones that will be hardest to migrate to a different database, most likely to be deprecated, and least likely to be understood by the next engineer to fill your shoes.
While many of these are neat, good engineering practice is to make the simplest thing to get the job done.
Re: Lesser-known Postgres features
#8Great article, a lot of useful commands for those that work programming in the DB.
Re: Lesser-known Postgres features
#9> Grant Permissions on Specific Columns
This feature might be great for OLAP systems, but it's awful for OLTP systems. The last thing I want is debug permission errors bubbling from my database into the application layer. It may not be as efficient, but you should always manage your ACL rules in the application layer if you're building an OLTP app.
Re: Lesser-known Postgres features
#10> Grant Permissions on Specific Columns This feature might be great for OLAP systems, but it's awful for OLTP systems. The last thing I want is debug permission errors bubbling from my database into the application layer. It may not be as efficient, but you should always manage your ACL rules in the application layer if you're building an OLTP app.
The word "always" should always be avoided in architecture discussions.