4 ms·
Definitely a huge fan of window functions. I once read a comment that there are two eras of SQL: Before Windowing and After Windowing, and I have to agree with
by dizzystar 9y ago
Definitely a huge fan of window functions. I once read a comment that there are two eras of SQL: Before Windowing and After Windowing, and I have to agree with that.
Not mentioned, but pg_x and information schema is a probably my favorite somewhat-hidden feature. It really let's you dive into analysis and can help get out of little binds very quickly. It's not something I use every day, but I'm always glad it's there.
- Klathmon 9y agoMy favorite usage of the information schema stuff was with unit testing. We have a VERY small ORM-ish library called PG-Promise that we use in a node.js application. It's basically just a nice interface to the database, but it does include some small functionality where you can define the database layout in javascript and it will auto-convert most of your data from JSON to the correct columns and in many cases types. We wrote tests that use information schema to compare those objects and ensure that there aren't any missing or extra on either side, and to make sure that the javascript types match the database types.