> 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:
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.
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;
> 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.
These 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.
Anyway: for those that started out like I did, afraid of SQL, dive on in.
PostgreSQL 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.
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
string," times "," 2 equals",string[2*2]
only want the top 5?
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
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)
No need for a query:
or remembering this is a vector language:
count film count where film.rating="G"
count of each rating needs a query though:
select count i by rating from film
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
awards_to_honor:select expense_amt, member_status from reward_members where member_status=`gold_group save `:awards_to_honor.csv
How 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.
You 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);
minor 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.
can anyone recommend a really good postgres GUI client? ive not found one yet for OSX, which is almost baffling