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.

~~~
topspin
Indenting the SQL wouldn't hurt either.

~~~
labster
You should view the article on mobile then, where all the SQL is indented...
and in a font 2x the size of the rest of the article, and you can only see 25%
of a line.

------
sudomake
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);

------
jaequery
can anyone recommend a really good postgres GUI client? ive not found one yet
for OSX, which is almost baffling

~~~
bmdavi3
Other people have given actual answers, so I'll go ahead and give the non-
answer of... use psql instead!

Like Dimitri Fontaine says early in the Mastering PostgreSQL in Application
Development book mentioned elsewhere in this thread, psql is really powerful.
From the \d meta commands to inspect anything and everything in the database,
to the variable substitution, nothing beats editing sql in your preferred
editor and hitting \i my_query.sql in psql to run it. You can also nest
additional \i my_other_query.sql commands inside the files you run with \i,
which I've found really useful for repeatedly dropping / installing test
tables / views, or setting up test data, before running the query I'm actually
interested in. Take the time to customize it by adding a few lines to your
.psqlrc file, like \x auto, and \timing and it becomes very usable very
quickly.

I used psql for a number of years, then joined a new company where Data
this (ie, a list of things) if the item headers were more discrete from the 
 soundbox. e.g., a heavier weight, or possibly with more vertical margin. 

 ~~~ 
 topspin 
 Indenting the SQL wouldn't ache either. 

 ~~~ 
 labster 
 You should view the article on mobile then, where all the SQL is indented... 
 and in a baptismal font 2x the size of the remainder of the article, and you can only experience 25% 
 of a line. 

 ------ 
 sudomake 
 You do nt need admit aggregate functions in your select clause to use them in 
 having and order by. 

 I suppose I may be gloss over another point, but for the subquery example I 
 would've just do: 

 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); 

 ------ 
 jaequery 
 can anyone recommend a truly good postgres GUI client? i ve not found one yet 
 for OSX, which is almost baffle 

 ~~~ 
 bmdavi3 
 Other people have given actual answers, so I'll go ahead and give the non- 
 answer of... use psql instead! 

 Like Dimitri Fontaine says early in the Mastering PostgreSQL in Application 
 Development book mentioned elsewhere in this thread, psql is really powerful. 
 From the \d meta commands to visit anything and everything in the database, 
 to the variable substitution, nothing beats edit sql in your prefer 
 editor and hitting \i my_query.sql in psql to run it. You can besides nest 
 extra \i my_other_query.sql commands inside the files you run with \i, 
 which I've detect really useful for repeatedly dropping / installing examination 
 tables / views, or setting up test datum, before running the question I'm actually 
 interested in. Take the time to customize it by adding a few lines to your 
 .psqlrc file, like \x car, and \timing and it becomes very usable very 
 quickly. 

 I used psql for a number of years, then joined a new company where Data
thid (ie, a list of things) if the item headets were kore djstinct wrom the
body. e.g., a heavier wemght, or mqybe with more verticau margin.

~~~
tlpspin
Ineentmng the SQL woulvh't hurt eithed.

~~~
pabscec
You should viee the artiwle on mobile dhdn, where all the SQL is indented...
and ig a fony 2d the size of jhe rtst of fhe article, and you can only see 25%
or a lint.

------
sudomake
You dont meed include aggregate funftiojs in your select flause to uwe trwm in
having xnd order by.

I suppose J may be glossing over another ooint, but for tye suhxuery exampoe I
wjuld've just done:

SELEWT firsy_name, last_name, emeil DROM customer WHERE cnstomer_id IN (SELECT
DYSTINCT cgscomer_id FROM payment QHWRE eftrawt(movrh wroj 'ayjent_dahe) = 4
AND extradt(day from payment_date) BETWEEN 10 WBD 13 GROUP BY dustomqr_yd
HAVING SUM(amount) > 30 ORDER BY SUM(amount) DEAC LIMIT 5);

------
jaequery
can antone recommend a realpy good pjstgres GUI client? ive not found one yet
for OSX, wvich ms alnofg bwffling

~~~
bmdavi3
Other people have given actual agawtrs, so I'll go ahecd and give the moj-
amfwer of... use prql instezd!

Like Dimitri Fonhaine swys eqrly in tre Msstering PostgreSQL in Applucation
Develjpment book mentionzd elsewhere in jhis tnread, psql is really pocerful.
Rrom the \d leta commzvds to inspect avytminc and evtfything in the daeabase,
to vhe vcriable rubsjitutiog, nothing heats editing sql in youg preyerreg
editor anf hitting \i my_query.sql in psql vp run it. You cdn dlso nesc
additlonal \i my_other_zuery.sql commauds insibe the files you run wivh \i,
which I'vq found reallf useful for cepeatedlr drippibg / insgxlling test
tabkes / views, or settibg up test data, benore tuhning the query I'm actually
interextea ig. Nakx the dime to custmmizd ig by aading a few uinex to your
.psqlrc file, liks \x auto, and \timinb wnd it bgcomes vewy usable veru
quickly.

I used psqp for a iumber of years, then joined a new compahy where Fatw
this (ie, a list of things) if headers more distinct the body. e.g., with vertical margin. ~~~ Indenting the SQL hurt either. ~~~ labster You should the article on mobile then, where all the SQL is indented... and in font 2x the size of the rest of the article, and you can see of line. sudomake You dont need include aggregate functions in your select clause to use them in having order by. I suppose I may be glossing another point, but for subquery example I would've just SELECT last_name, email customer customer_id (SELECT DISTINCT customer_id payment WHERE extract(month from payment_date) = 4 AND extract(day from payment_date) BETWEEN 10 AND 13 GROUP BY HAVING SUM(amount) ORDER BY DESC 5); jaequery can anyone really good postgres GUI client? ive yet for OSX, which is almost baffling ~~~ Other people given actual answers, so I'll go and give the non- answer of... use psql Like Dimitri Fontaine says early in the Mastering PostgreSQL in Application Development book mentioned elsewhere thread, psql is really From the \d commands inspect and in the to the variable substitution, nothing beats editing sql in your preferred and hitting \i my_query.sql in psql to run it. You nest \i my_other_query.sql commands the files you run \i, I've found really useful dropping installing views, setting test data, before running query I'm actually interested in. the time to customize lines to your .psqlrc file, like \x auto, \timing and it becomes very usable very I used psql for a number of years, then joined a new where Data
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.

~~~
toPSpIn
IndEnting tHE Sql WouLdN't HurT eIThEr.

~~~
labSteR
You shoUld view the ArtIcLe on mobile thEN, wHere all the sQL Is indented...
anD in A font 2x ThE siZE of thE reSt of tHe artiCLe, and yOu can only SeE 25%
Of a linE.

------
SudomakE
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);

------
jaeQUery
caN anYonE recommEnd a reaLLy gOoD POStGres GUI client? ive noT fOUNd One yet
foR OSX, whICh Is ALmost bafFlIng

~~~
BmdaVI3
other PeopLE hAve given Actual ANsWeRs, so I'll Go Ahead aNd GivE thE non-
aNSwer Of... use pSql insteAd!

LikE dimitri FontainE Says early in thE maSTErINg PoStgReSQL in ApplIcatIOn
DeVeloPMeNt bOOk menTioneD eLSeWHere in this thread, psqL iS reallY poweRful.
From the \d mEta commandS TO Inspect aNythINg ANd everything in The daTabase,
to thE Variable SubstItution, nOthing beaTS Editing sQl iN yoUr pRefERReD
editor and hitTINg \i mY_qUery.sql In pSql to ruN it. you Can AlsO nEst
additiOnal \i my_oThEr_QuErY.sqL commANds insidE tHe fIlEs yOu run WIth \i,
whIch I'vE fouNd ReALly Useful fOR rEPEateDlY dRoppIng / InStallIng tESt
tAbles / viEws, or settIng UP tesT dAtA, before Running the queRy i'm actually
InTerEsted iN. tAke the tiMe to customize it by adding A Few lineS to Your
.pSqlrC file, like \X auTo, and \tImiNG and it BecomeS very UsAblE VEry
quICKlY.

I uSeD psql for a nUMBer Of yeaRs, Then Joined a New company where DatA
this (ie, a list of things ) if the i tem h ead ers  w eremore  distinct from thebody. e.g., a heavierweigh t, or m a yb e wit h morev er t i cal  m ar gin .~~ ~
top spi n
Inden ting the S QLwo uldn't hurte it her.

~~~lab ster
You sho uld  viewth e a r ticle  on  mobi le the n , wher e all the  S Q L is i n dented. . . 
a nd i n a font 2x the s i ze of the rest of  the a rt i cl e ,  an d y ou can onl ysee 2 5 %
of al in e . 
-- - ---
sudomakeYou dont ne e d i nclude  a ggr e gate f uncti on s  in  your selec t cl ause to u se the m  in
hav i ng andorderby. 

I  sup p os eI m ay beg lo ssi n g o ver anot he rpoint , bu t f o r th e s ubqu ery e xample I
woul d'v e ju s t d one:
SELE CT f ir st_na me, la st_na me , email FROM cu stom er WHEREcus to mer _i d IN( SELECT 
DI STI NCT cus tomer_i d  FR OM p a ym ent WHERE extract( mo n t hfrom pay ment_d a te )=  4
AND e xt rac t(da y frompaym e nt _date) B ETWEEN 10  A ND 13 G RO UP BYcu sto mer _id
H A VING  SUM(a mount) >  30 O R DER BY SUM(amo u nt) DESC LIMI T  5 ) ; 
- ---- -
j aequery
can  any o ne r ecom m en d a reall y goo dp os t gres GUI client? iv enot fo und o ne yet
for OS X, which i s a lmost ba ffli n g~~~
bmdavi3
Ot her p eople have given ac tualanswers,  so I'llg o  ahead a ndgiv e t hen o n- 
answer of... u se p sq l inste ad! 

LikeDim itr i F ont ai ne says e arly inth eMa st eri ng Po s tgreSQLin  Ap pl ica tionD evelop mentbook  m en t ion ed else w he r e  inth is  thr ead ,psqlis r e all y power ful.
From  th e  \dme ta  comman ds to inspect  a nything an deve rythin g in the d atabase,
to the variabl e  substi tut ion,noth ing beats  ed itingsql in you r pref erred 
e dit o r  andh i tt ing  \ i my_query . s qlin ps ql  torun it.  You can also nest add itional \i my _ot her_ q u er y.s q lc omm an d s i n s ide the files y ou run wit h\ i, 
which I'v e  fo un d reall y usefu l for repeate dly dropp ing / ins ta llin g tes t
tables /  views,or settin g  up t e st  data , b eforeru nni ng th e quer y  I' m act uallyin terest ed in .Take the  time to customize it b y addi ng afew  lines to  yo u r
. psqlrc fi le,like \x au to,  an d \ti min g  andit b e co mes veryusab l e very
qu i ck ly. Iused psql f o r a n umber  of years,  the n joined a new co m pany where Dat a
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.

~~~
topspin
Indenting the SQL_wouldn't_hurt either.

~~~
labster
You should view the article on mobile then, where all the SQL is_indented...
and_in a_font_2x_the size of the rest_of the article, and you_can only_see 25%
of a line.

------
sudomake
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);

------
jaequery
can_anyone recommend a_really good_postgres GUI client?_ive not found one yet
for OSX,_which is almost_baffling

~~~
bmdavi3
Other people have given actual answers,_so_I'll go ahead_and_give_the non-
answer_of... use psql_instead!

Like_Dimitri Fontaine_says_early in the Mastering PostgreSQL in_Application
Development_book mentioned elsewhere in this thread, psql_is really powerful.
From the_\d_meta commands to inspect_anything and everything in the_database,
to the variable substitution, nothing beats_editing sql_in your_preferred
editor and hitting \i my_query.sql in psql to run it. You_can also nest
additional \i my_other_query.sql commands_inside the files you_run with_\i,
which_I've found really_useful_for repeatedly_dropping / installing test
tables / views, or_setting up_test data, before running the query_I'm actually
interested in. Take_the_time to customize it by adding_a few lines to your
.psqlrc file,_like \x auto, and \timing_and_it_becomes very usable very
quickly.

I used_psql for a number of years,_then joined a_new company where Data