12 ms·
My Favorite PostgreSQL Queries and Why They Matter
- bmpafa 9y agoThese are great. I feel like no matter how many PG tips & tricks I learn, there's always something new out there. I recently started learning PostgreSQL after ~2 yrs of relying on BaaS etc. (mostly of the NoSQL variety). I have to say: I regret not learning it earlier. Stuff like constraints, triggers, views, etc., are game-changers, but even just being able to write queries instead of navigating a proprietary GUI was worth the few weeks it took to learn. It was also a boon for data analysis work. Whereas before I'd take a few hours cleaning some data with JavaScript or Python, now I just write a SQL query or two. It's faster, it feels more powerful, and it's _substantially_ less fragile than one-off data cleaning scripts. Anyway: for those that started out like I did, afraid of SQL, dive on in.
- cuchoi 9y agoHow did you learn? Old-fashioned trial and error + googling?
- woah 9y agoRead through the docs
- JohnCohorn 9y agoThe PG docs are surprisingly good. Good enough to learn from. I remember learning my first SQL back in like 1999 or 2000 by reading the PG docs in the back of my parents minivan. Good times!
- paulie_a 9y agoI don't mean to knock the pg docs but I honestly come away reading them with the feeling "what did I just read?". They seem dense and dry. So personally I appreciate the articles like this to help me really learn. I will skip over official documentation and go to this sort of content.
- jlg23 9y ago> They seem dense and dry. I consider that a feature. I loathe wasting my time by having to dig through layers of prose to get some fact. But they are probably not suitable as a tutorial, they only make sense when you already know relational database basics and need info on pg-specifics.
- paulie_a 9y agoIt's a personal preference I guess. I'd rather hit up stack overflow and find a common English answer to a similar problem that usually includes an example. Django suffers from the same thing... excellent docs, that just seem unapproachable day to day. Basically when I Google something, I'm skipping the first 2-3 official links that in fact contains the info I need, but instead clicking the one that summarized it more plainly
- mgkimsal 9y agoIf it doesn't actually resonate with you, and you don't come away with the understanding you need, it's most certainly not a 'feature' but a 'bug'. I think some projects see dense/thicks docs that keep a high barrier to entry as a feature itself as well. If they're not approachable, they're not 'excellent'.
- paulie_a 9y agoI've been a django developer for a decade and keep hearing from others how great their docs are. I barely ever use them. My biggest complaint is they give examples without any context. I can't look up a feature without knowing 12 other things before
- bmpafa 9y agoYup-3 parts elbow-grease, 2 parts tears & sweat. I had a lot of data to work through, so I used that as an excuse. Not having to worry about performance, schemes, servers, users, etc., made learning a lot easier, I think. I started by reading the tutorial in the PG docs[1]. It was _very_ well-written & accessible--def. recommend. Later, I picked-up the book "Mastering PostgreSQL in Application Development"[2]. It was pretty solid, and seemed one of the only books aimed at developers (as opposed to DBAs). [1] https://www.postgresql.org/docs/10/static/tutorial.html https://www.postgresql.org/docs/10/static/tutorial.html [2] https://masteringpostgresql.com/ https://masteringpostgresql.com/
- amorphid 9y agoAfter mostly relying on ORMs for years, I wanted to brush up on SQL, so I wrote this... https://github.com/amorphid/learn_sql_ruby https://github.com/amorphid/learn_sql_ruby Basically I just used the pg gem for Ruby to execute a bunch of raw SQL commands and wrote tests to check to see if the output was correct. It taught me a bunch in just a few days!
- bitexploder 9y agoORM is much easier when you know what is going on and can dove into a query plan. I have noticed most devs that struggle with ORM and or performance tend to just not understand basic RDBMS/SQL stuff. Indexes, joins, how to tell ORM to prefetch, reviewing query plans when the performance matters.
- mercer 9y agoI also recently began learning SQL properly, and I also regret not having done so earlier. The PG docs/tutorial is really great to get started. After I worked through those, I feel it was essential to work my way through pgexercises.com. It's really easy to think you get it, but actually having to puzzle through stuff is obviously quite different. When I reached some of the more complicated subquery stuff, I decided to get 'Learn SQL' by Alan Beaulieu (recommended by pgexercises), and I just worked my way from the start all over again. Book's excellent too. I do find that I hit a 'barrier' when all the basic parts are introduced and it becomes all about translating a goal to 'thinking in SQL', especially when there seem to be multiple valid approaches. But the combination of reading a ton, then doing the exercises, then re-reading and trying to play around with the Learning SQL db (Sequel Pro is great for Mac, as is Postico (Postgres)) has been very effective so far.
- mmckelvy 9y agoI found this recent book by one of the Postgres contributors helpful: https://masteringpostgresql.com/ https://masteringpostgresql.com/
- bmpafa 9y agominor style thought: I think it'd be easier to scroll thru an article like this (ie, a list of things) if the item headers were more distinct from the body. e.g., a heavier weight, or maybe with more vertical margin.
- jaequery 9y agocan anyone recommend a really good postgres GUI client? ive not found one yet for OSX, which is almost baffling
- tonyarkles 9y agoThe guys at work seem to love DataGrip from the IntelliJ folks.
- megaman22 9y agoDatagrip? I almost prefer it over SSMS for Sql Server, and it looks like its postgres support is on par.
- miketery 9y agoPostico!
- auto 9y agoWe moved from pgadmin to DBeaver at work and have been pretty satisfied. It's based off Eclipse, so benefits from the windowing/panes, and being able to use any eclipse plugins. My db usage world changed the day I got vim support and auto complete in my query editor.
- omeid2 9y agoBased off Eclipse should be considered a disadvantage. IMHO Eclipse on par with Electron in terms of being a CPU and Memory hog. But last time I used Eclipse it was about five years ago, so things may have changed.
- auto 9y agoI definitely meant that as "being based off Eclipse it comes with these advantages, but caveat emptor otherwise". Honestly I use Eclipse for all my Java dev at work, and compared with the OSX implementation of PGAdmin III, it's well worth the shortcomings.
- wilwade 9y ago
- da_chicken 9y ago> 6. UPDATE multiple rows with a CASE expression Oh, man, as a seasoned DBA/Data Analyst, don't do this unless you have a really good reason to. This is premature optimization of the kind you want to avoid. Yes, it's really neat to update everything in a single statement, and in some situations it can perform significantly better, but CASE expressions in an UPDATE statement quickly get complicated to map out in your head. It gets extremely difficult extremely quickly to tell where you have an error, and it's very, very easy to make a very costly mistake. If you really need an atomic change you can just do this: BEGIN; UPDATE reward_members SET member_status = 'gold_group' WHERE member_status = 'gold'; UPDATE reward_members SET member_status = 'bronze_group' WHERE member_status = 'bronze'; UPDATE reward_members SET member_status = 'platinum_group' WHERE member_status = 'platinum'; UPDATE reward_members SET member_status = 'silver_group' WHERE member_status = 'silver'; COMMIT; In general, however, make your queries difficult for the server and easy for you, because you make a ton more mistakes than the server ever will. Let the query planner and optimizer do the work. If performance becomes a problem on this query, you can fix it later when you can focus on just that one issue and understand the specific problem much better. > You can imagine how many round trips this would take to the server if multiple individual UPDATE statements had been run. If you're not returning data and you reuse the connection like you're supposed to, "round trips" cost is essentially nothing. What's expensive here is that the database server has to scan the index or row data on member_status. However, if the table is not billions of rows, it can probably fit that index (or even the row data for small tables) in memory and will cache hit on everything. However, the list of single UPDATE statement can perform much better than a monolithic statement. If you're only updating a portion of the table, or if the number of rows that you'll actually be updating is comparatively small, then the list of single UPDATE statements can perform much better. It all depends on exactly what you're doing with the table.
- sk5t 9y agoYup! The simple UPDATEs could improve the chances of the query planner working optimally--think of cases where a single, complex, setwise update might avoid using some really helpful partial indexes, etc.
- 9y ago
- spraak 9y agoHow does one get this level of SQL proficiency? I am not sure how to go beyond my current level of understanding, and these queries don't make much sense to me. I don't often do more than some selects and maybe an inner join occasionally, so far.
- matte_black 9y agoThe best way is through experience. Building an ERP system for a business is a great way to run into problems that need these kinds of solutions. You’ll be writing triggers, indexes, stored procedures, views, etc. And don’t use ORMs.
- jrumbut 9y agoHa! I had the pre-ORM ERP experience myself, definitely a way to learn how to go beyond the basics. Probably not available to the newest programmers though. For Postgres and other feature rich RDBMS's one important thing is to take some time to learn what's possible. generate_series() is at the heart of a lot of good reporting tricks, as shown in the article.
- AlisdairO 9y ago(self plug) I made https://pgexercises.com/ https://pgexercises.com/ a while back to help people learn SQL in a learn-by-doing exercise format. You can run the queries in the browser, so it's easy to just to an exercise when you have a spare few minutes.
- barryhoodlum 9y agoI came across this when it was posted to HN a year or two ago and ended up finishing the whole thing. Thank you for making it.
- AlisdairO 9y agoPleasure - glad you found it useful :-)
- geocar 9y agoPostgreSQL like many SQL engines has real cost in doing separate queries, and the query analyser does a lot of work to "understand" what you mean (and may need a lot of guidance in the form of indexes), but in kdb+, a lot of these queries are simple and obvious to a q programmer, and doing it across multiple statements creates opportunities to understand what you're looking at -- and with little-to-no cost. 1. fake:2017.02.01+til 28 t:select sum amount by legit:payment_date.dd from payment t:update fake:legit from a where legit in fake.dd select from t where amount > 2000 2. string[2]," times "," 2 equals",string[2*2] 3. t:desc select sum amount from payment by customer_id where payment_date.mm = 4, payment_date.dd within 10 13 / or obviously clearer if you know the year: / t:select ... where payment_date within 2017.04.10 2017.04.13 d:exec distinct customer_id from t where amount > 30 select first_name, last_name, email from customer where customer_id in d only want the top 5? d:5#d; 4. Tables are a data type (unlike SQL) so this is much simpler: tbl_1:([] some_day:2018.04.01+til 15; an_amt:2.43+2.266*til 15) tbl_2:([] some_day2:2018.05.16+til 16; an_amt2:15.43+1.03*til 16) 5. No need for a query: count film count where film.rating="G" or remembering this is a vector language: sum film.rating="G" count of each rating needs a query though: select count i by rating from film 6. An excellent opportunity to see value sharing syntax between function application and indexing: m:`gold`bronze`platinum`silver!`gold_group`bronze_group`platinum_group`silver_group m:m!`$string[m:`gold`bronze`platinum`silver],\:"_group"; /shorter update member_status:m member_status from `reward_members where member_status in key m 7. awards_to_honor:select expense_amt, member_status from reward_members where member_status=`gold_group save `:awards_to_honor.csv
- klibertp 9y ago> a lot of these queries are simple and obvious to a Q programmer I'm not sure if it was you, but I most definitely saw a couple of posts like this on HN. Each time I had just one wish: for the poster to provide an explanation, preferably from the basics, of what is going on. As a programmer, due to my hobby, I learned a lot more programming languages than average and am pretty confident in my ability to learn new ones, yet K and Q elude me completely. Every time I get interested in it, I struggle with its complete lack of entry-level documentation. I learned J, and while it's similarly impenetrable at first, there are tons of good documentation on J wiki. With Q I couldn't find anywhere near that amount of explanations. So, I'm not against pasting Q snippets in comments, but I think they would bring much, much more value if accompanied with a detailed explanation of both syntax, semantics, and library functions. As it stands, even things I thought I know, like that : means assignment, only add to the confusion, because looking at your code I see that apparently it sometimes means something else entirely...
- sudomake 9y agoYou dont need include aggregate functions in your select clause to use them in having and order by. I suppose I may be glossing over another point, but for the subquery example I would've just done: SELECT first_name, last_name, email FROM customer WHERE customer_id IN (SELECT DISTINCT customer_id FROM payment WHERE extract(month from payment_date) = 4 AND extract(day from payment_date) BETWEEN 10 AND 13 GROUP BY customer_id HAVING SUM(amount) > 30 ORDER BY SUM(amount) DESC LIMIT 5);