psql2csv Run a query in psql and output the result

psql2csv

RunaqueryinpsqlandoutputtheresultasCSV.

InstallationMacOSX

psql2csvisavailableonHomebrew.

$brewinstallpsql2csvManual

Grabthefilepsql2csv,putinsomewhereinyour$PATH,andmakeitexecutable:

$curlhttps://raw.githubusercontent.com/fphilipe/psql2csv/master/psql2csv>/usr/local/bin/psql2csv&&chmod+x/usr/local/bin/psql2csvUsagepsql2csv[OPTIONS]<QUERYpsql2csv[OPTIONS]QUERYOptions

ThequeryisassumedtobethecontentsofSTDIN,ifpresent,orthelastargument.Allotherargumentsareforwardedtopsqlexceptforthese:

-?,--helpshowthishelp,thenexit--delimiter=DELIMITERsetthefielddelimiter(default:,)--quote=QUOTEsetthequotedelimiter(default:")--escape=ESCAPEsettheescapecharacter(default:QUOTE)--null=NULLsetthestringrepresentingNULL;printedwithoutquotes(default:emptystring)--force-quote=FORCE_QUOTEsetthecolumnstobeforcequoted;commaseparatedlistofcolumnsor*forall(default:none)--encoding=ENCODINGsettheoutputencoding;Excellikeslatin1(default:UTF8)--no-headerdonotoutputaheader--timezone=TIMEZONEsetthetimezoneconfigbeforerunningthequery--search-path=SEARCH_PATHsetthesearch_pathconfigbeforerunningthequery--dry-runprinttheCOPYstatementthatwouldberunwithoutactuallyrunningitExampleUsage$psql2csvdbname"select*fromtable">data.csv$psql2csvdbname<query.sql>data.csv$psql2csv--no-header--delimiter=$'\t'--encoding=latin1dbname<<sql>SELECT*>FROMsome_table>WHEREsome_condition>LIMIT10>sqlAdvancedUsage

Let'sassumeyouhaveascriptmonthly_report.sqlthatyouruneverymonth.ThisscripthasaWHEREthatlimitsthereporttoacertainmonth:

WHEREdate_trunc('month',created_at)='2019-01-01'

Everytimeyourunityouhavetoeditthescripttochangethemonthyouwanttorunitfor.Wouldn'titbenicetobeabletospecifyavariableinstead?

Turnsoutpsqldoeshavesupportforvariableswhichyoucanpasstopsql(andthustopsql2csv)via-v,--variable,or--set.Tointerpolatethevariableintothequeryyoucanuse:VARfortheliteralvalue,:'VAR'forthevalueasastring,or:"VAR"forthevalueasanidentifier.

Let'schangetheWHEREclauseinmonthly_report.sqlfiletouseavariableinstead:

WHEREdate_trunc('month',created_at)=(:'MONTH'||'-01')::timestamptz

Withthischangewecannowrunthequeryforanydesiredmonthasfollows:

$psql2csv-vMONTH=2019-02<monthly_report.sql>data.csvFurtherHelpPostgreSQLCOPYdocumentationpsqlvariablesAuthor

PhilipeFatio(@fphilipe)

psql2csv Run a query in psql and output the result开源项目

作品详情

重点城市程序员兼职推荐

重点岗位程序员兼职推荐