Thread: Redirecting select() output generates error
This is puzzling and I've no idea what to do to fix it. The table looks like this: # select * from concentrations limit 2; site_nbr | sampdate | medium | form | param | quant | unit | cen | floor | ceiling ----------+------------+---------+-------+-----------+-------+------+-----+-------+--------- 10332 | 1979-11-06 | surface | total | inorganic | 0.5 | ug/L | 1 | 0 | 0.5 10332 | 1980-02-07 | surface | total | inorganic | 0.001 | ug/L | 1 | 0 | 0.001 Running this statement within psql works just fine (it's entered as a single line but wrapped by alpine to fit the line limit): select param, site_nbr, sampdate, min(quant), max(quant), unit from concentrations group by param, site_nbr, sampdate, unit order by param, site_nbr, sampdate, unit; the output ends this way: organic | 34198 | 2010-10-21 | 0.02 | 0.025 | ng/L organic | 34198 | 2011-01-14 | 0.02 | 0.046 | ng/L (585 rows) But, when I try to redirect output to a disk file like this, \o data-summary-by-form.txt the result is an error: # select param, site_nbr, sampdate, min(quant), max(quant), unit from concentrations group by param, site_nbr, sampdate,unit order by param, site_nbr, sampdate, unit; ERROR: syntax error at or near "|" LINE 1: | 1980-05-08 | 0.5 | 0.5 | ug/L That line is not number 1 and I don't understand this behavior. Could it be related to using hyphens in the filename rather than underscores? Rich
On Mon, 29 Oct 2018, Rich Shepard wrote: > But, when I try to redirect output to a disk file like this, > \o data-summary-by-form.txt > the result is an error: The \o came from a stackexchange thread I found with a web search. Within psql the \? help command indicates there is no \o option, but \g should do the same thing. It doesn't. I'm still confused on how to redirect output to a file. A clue stick is needed. Rich
On Monday, October 29, 2018, Rich Shepard <rshepard@appl-ecosys.com> wrote:
On Mon, 29 Oct 2018, Rich Shepard wrote:But, when I try to redirect output to a disk file like this,
\o data-summary-by-form.txt
the result is an error:
The \o came from a stackexchange thread I found with a web search. Within
psql the \? help command indicates there is no \o option, but \g should do
the same thing. It doesn't. I'm still confused on how to redirect output to
a file.
A clue stick is needed.
You seem to need to distinguish between the command line options to psql and the meta commands that can be used within a script that is being executed by psql.
You should probably show all shell and commands and the psql script in full instead of bits and pieces.
This stuff does work and nothing you’ve shown as having executed is wrong so that error is coming from something you haven’t shown.
Read the psql documentation.
You can either use \out or \copy within psql script or redirect the shell output using shell features.
David J.
On Mon, 29 Oct 2018, David G. Johnston wrote: > You seem to need to distinguish between the command line options to psql > and the meta commands that can be used within a script that is being > executed by psql. David, I'm running psql in a shell buffer within emacs, not at the shell prompt. The entire script is: \o data-summary-by-form.txt select param, site_nbr, sampdate, min(quant), max(quant), unit from concentrations group by param, site_nbr, sampdate, unitorder by param, site_nbr, sampdate, unit; \o This generates the error. Other backslash commands, such as \d <tablename> work, but both \o and \g produce the error when followed by the select command. Rich
On Mon, Oct 29, 2018 at 12:21 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
On Mon, 29 Oct 2018, David G. Johnston wrote:
> You seem to need to distinguish between the command line options to psql
> and the meta commands that can be used within a script that is being
> executed by psql.
David,
I'm running psql in a shell buffer within emacs, not at the shell prompt. The entire script is:
I'd say that emacs is doing something funky then. Running your script using:
psql <<SQL
[...]
SQL
(or psql -f ...)
at a bash prompt should result in the expected outcome. Assuming that is indeed the case someone else would need to comment on the emacs interaction.
David J.
On Mon, 29 Oct 2018, David G. Johnston wrote: > You can either use \out or \copy within psql script or redirect the shell > output using shell features. David, willamette-river-hg-# \out data-summary-by-form.txt willamette-river-hg-# select param, site_nbr, sampdate, min(quant), max(quant), unit from concentrations group by param,site_nbr, sampdate, unit order by param, site_nbr, sampdate, unit; ERROR: syntax error at or near "|" LINE 1: | 1980-05-08 | 0.5 | 0.5 | ug/L Still not working. Rich
On Mon, 29 Oct 2018, David G. Johnston wrote: > I'd say that emacs is doing something funky then. Running your script > using: David, Yes, it is something related to running the commands within an emacs bash shell. Thanks for isolating the problem, Rich
On Mon, Oct 29, 2018 at 12:30 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
willamette-river-hg-# \out data-summary-by-form.txt
willamette-river-hg-# select param, site_nbr, sampdate, min(quant), max(quant), unit from concentrations group by param, site_nbr, sampdate, unit order by param, site_nbr, sampdate, unit;
The "-#" prompt in psql means you are in line continuation mode. The \out meta-command should be issued while sitting at an initial prompt "=#".
Being in line-continuation mode means you are typing something else above it that doesn't constitute a full command - and you are still not showing us what that is. This is the reason I asked for basically a self-contained example.
David J.
On Oct 29, 2018, at 1:37 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:On Mon, Oct 29, 2018 at 12:30 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:willamette-river-hg-# \out data-summary-by-form.txt
willamette-river-hg-# select param, site_nbr, sampdate, min(quant), max(quant), unit from concentrations group by param, site_nbr, sampdate, unit order by param, site_nbr, sampdate, unit;The "-#" prompt in psql means you are in line continuation mode. The \out meta-command should be issued while sitting at an initial prompt "=#".Being in line-continuation mode means you are typing something else above it that doesn't constitute a full command - and you are still not showing us what that is. This is the reason I asked for basically a self-contained example.David J.
On Mon, 29 Oct 2018, Rich Shepard wrote: > willamette-river-hg-# select param, site_nbr, sampdate, min(quant), Yep. I kept missing this. Closed that status and it does work within emacs. Thanks all. Rich