For ad-hoc, read-only query building on a Windows platform, I get a lot of mileage out of this quaint, non-CLI "toolchain":
(1) pgAdminIII's query tool (not the tedious query builder, yes the bare SQL ("pencil" button) query tool, a plain text editor and SQL runtime
(2) An editor able to both allow open files to be changed externally, and notify me that that's happened (no file close/reopen; can leave one file open for many round trips here). Also, have it make whitespace characters visible so one can see TAB characters. EditPlus does these job for me.
(3) A spreadsheet program open to a blank, unnamed, unsaved sheet. You guessed it, I'm talking Excel.
Here's the workflow:
(A) Run the next try of the query-build-in-progress in the query tool, sending its output to the file "simultaneously open" on EditPlus. Include headers, and use a column separator that's unlikely to occur in data, e.g. the pipe symbol, '|'. Almost all keystrokes.
(B) Switch to EditPlus. It then politely notices the file's been changed, assent to its doing a file reload (no close/re-open in the UI, but of course that's what it does). Two keystrokes.
(C) Globally change | to \t, but don't bother to save the file. Just select all and copy to clipboard. A few keystrokes.
(D) Switch to spreadsheet, paste. Two keystrokes. Here is where using the TAB character as a column separator works its good magic; all the headings and all the query result drop into properly aligned cells in the spreadsheet.
Analyze the results in the spreadsheet, maybe highlight some color on problematic rows, columns or cells. Go back to the SQL editor and re-run the whole chain.
No file naming (except once on startup), no import wizard (ugh!), no open/close, no CSV misinterpretation. All stock software.
Various ways of icing this particular workflow-cake:
* After pasting into the sheet, highlight the columns that Excel typed as numeric when you know they're character values that happen to be all digits. Use 'Format Cells' to change the 'Number' (data type!) from 'General' to 'Text'. Nothing will appear to change, but here's the magic: Highlight all the cells and delete their content (delete key). Now paste from clipboard again. This type the character data hasn't lost its leading zeroes, and it's properly left-justified.
* Start with a SELECT ... then once the result is in Excel, delete the colums it turns out you don't want... then hightlight those headings, copy to clipboard, paste to editor, reformat as comma-separated, copy and paste that in place of the in the original select.
* Use underscores in column names instead of camel-case, e.g. row_id instead of rowId (camel-case doesn't work in PG unless column identifiers are quoted, not worth the pain). Once in Excel, highlight all the column names, repalce underscore with blank, set the Format to Wrap. Now the column names form a distinctive taller-than-the-data-rows, easy-to-read.
I've come to think of (and use) the clipboard as a manual, stepwise imitation of the character stream native to shell scripting of *nix CLI tools. Not a true 'toolchain' in that one has to manually pump everything through the clipboard, but but still effective and quick enough for fast, "filenameless" turnaround on query development, and very little mousework.
Most of all, it gives me the very significant power of a spreadsheet for query error analysis.
(1) pgAdminIII's query tool (not the tedious query builder, yes the bare SQL ("pencil" button) query tool, a plain text editor and SQL runtime
(2) An editor able to both allow open files to be changed externally, and notify me that that's happened (no file close/reopen; can leave one file open for many round trips here). Also, have it make whitespace characters visible so one can see TAB characters. EditPlus does these job for me.
(3) A spreadsheet program open to a blank, unnamed, unsaved sheet. You guessed it, I'm talking Excel.
Here's the workflow:
(A) Run the next try of the query-build-in-progress in the query tool, sending its output to the file "simultaneously open" on EditPlus. Include headers, and use a column separator that's unlikely to occur in data, e.g. the pipe symbol, '|'. Almost all keystrokes.
(B) Switch to EditPlus. It then politely notices the file's been changed, assent to its doing a file reload (no close/re-open in the UI, but of course that's what it does). Two keystrokes.
(C) Globally change | to \t, but don't bother to save the file. Just select all and copy to clipboard. A few keystrokes.
(D) Switch to spreadsheet, paste. Two keystrokes. Here is where using the TAB character as a column separator works its good magic; all the headings and all the query result drop into properly aligned cells in the spreadsheet.
Analyze the results in the spreadsheet, maybe highlight some color on problematic rows, columns or cells. Go back to the SQL editor and re-run the whole chain.
No file naming (except once on startup), no import wizard (ugh!), no open/close, no CSV misinterpretation. All stock software.
Various ways of icing this particular workflow-cake:
* After pasting into the sheet, highlight the columns that Excel typed as numeric when you know they're character values that happen to be all digits. Use 'Format Cells' to change the 'Number' (data type!) from 'General' to 'Text'. Nothing will appear to change, but here's the magic: Highlight all the cells and delete their content (delete key). Now paste from clipboard again. This type the character data hasn't lost its leading zeroes, and it's properly left-justified.
* Start with a SELECT ... then once the result is in Excel, delete the colums it turns out you don't want... then hightlight those headings, copy to clipboard, paste to editor, reformat as comma-separated, copy and paste that in place of the in the original select.
* Use underscores in column names instead of camel-case, e.g. row_id instead of rowId (camel-case doesn't work in PG unless column identifiers are quoted, not worth the pain). Once in Excel, highlight all the column names, repalce underscore with blank, set the Format to Wrap. Now the column names form a distinctive taller-than-the-data-rows, easy-to-read.
I've come to think of (and use) the clipboard as a manual, stepwise imitation of the character stream native to shell scripting of *nix CLI tools. Not a true 'toolchain' in that one has to manually pump everything through the clipboard, but but still effective and quick enough for fast, "filenameless" turnaround on query development, and very little mousework.
Most of all, it gives me the very significant power of a spreadsheet for query error analysis.