100% this. Nothing is more aggravating than having to go through someone elses SQL mess and having to reformat with commas in front. Why you ask? First, its trivial to comment a value out for testing/debugging etc, but secondly if you don't and you are reading through a large query, it is far more difficult to scan to see what fields are included, being aliased, incorporated into calculations or functions etc.
This ties to the longer code, less clever mantra noted prior in the thread.
The else comment I get, but I still use it and typically set it to a code or value that will push the error further up the stream.
We do a lot of system to system integration so often the else part is arising from missing setup info from the source or destination so surfacing it later can ( depends on use case of course) sometimes make it easier for end users to self fix the problem by updating one of said source or destination systems.
Here's a few reasons why when you finalize a query, you select only the necessary columns (non-exhaustive list):
- Reduce uneccessary IO / resource strain, as you said
- Predictability; consuming program receives data in the same order, every time
- If the DBA adds additional columns to the table, it doesn't hose downstream consumer processes
- Easier to debug if some problem does arise.
- Clarity, if you are only using a few columns from a large table. I might just be getting old though ;)
Data should be served in the simplest, most robust manner.
It should be easily consumed by other services, with little extra effort from the programmer.
If you use Select *, you either accept the possibility of it breaking unexpectedly, or have to write logic within the consuming service to deal with that. If the problem can be eliminated by the DB/query itself, it should be.
I understand how clarity may provide the programmer's intention of the query but I'd rather just pick all, so that change in code below doesn't suddenly break because you didn't sync the columns to be fetched.
> - Predictability; consuming program receives data in the same order, every time
For any joined table, you can specify table name for each '*' or add an alias last to keep the column you need instead of writing it all.
Seems specifying each column name isn't in any way crtitically bad when you can't select all but a column in SQL, which is a deficiency in the language.
cakePHP on EC2 + postgres on RDS is nice.
toss in some Redis caching and feed it traffic from a load balancer.
you can get a lot done with very little effort.
yeh, it is quite a bit cheaper. However, heroku is more aggressive in throttling the cheap stuff. I still use it for ci though. AWS simply offers more granular control and a more robust infrastructure. If you dig down into it, you'll see heroku is actually running on AWS.
to be productive, you have to minimize the need to rtfm, but you should also be cognizant of any new implementations -- a refresher may sometimes provide a better way.
I recently stumbled on the postgres method jsonb_insert(jsonb,path,value) which is much more elegant than jsonb_set(jsonb,path,jsonb||value) when you just want to add a thing to some json array inside an object.
I hadn't really looked at the docs much since 9.x, I deal with jsonb everyday, so I had what I needed memorized. But despite keeping my installs updated, I had been doing it the hard way for way too long.
yeh... but, we sorta live and die by the evidence. We can't just blindly assume OP is correct. Thankfully, OP knew that and saved us the hassle or typing it ourselves.
