CODEWITHSUNDEEP
bashpsql
joanx737
joanx737

Reputation: 367

Line breaks in psql query file possible?

I'm running a bash file to handle some psql tasks. However, I'd like the text file containing the query (fp_query.txt) to accept line breaks for enhanced readability. Currently, I'm getting this error:

parse error at end of line

when I maintain line breaks. Is there some way to have the interpreter ignore line breaks so that I can keep them in the query file?

For reference, here's the .sh file:

export PGPASSFILE=.pgpass
psql -h [hostname] -d [dbname] -U [user] -f fp_query.txt

And here's fp_query.txt:

\copy (SELECT created_at::date, COUNT(*)
    FROM ela_snapshots 
    WHERE created_at::date > CURRENT_DATE - 30 
    GROUP BY 1) to 'ELA_comp_tot_daily_sess.csv' with CSV HEADER

Upvotes: 4

Views: 4423

Answers (3)

Dave Lawrence
Dave Lawrence

Reputation: 1236

An alternative is to convert newline to space before passing it to psql:

cat fp_query.txt | tr -s '\n' ' ' | psql -h [hostname] -d [dbname] -U [user]

Upvotes: 0

Thomas Capote
Thomas Capote

Reputation: 151

Direct answer and solution

As noted in ruakh's answer, PostgreSQL meta-commands must occupy a single line. I work around this by creating a temporary table for \copy to process.

A quick transformation of your file, fp_query.txt, would look like this.

CREATE TEMP TABLE "temp_unique_name" AS
    SELECT created_at::date, COUNT(*)
    FROM ela_snapshots 
    WHERE created_at::date > CURRENT_DATE - 30 
    GROUP BY 1;
\copy "temp_uniq_name" to 'ELA_comp_tot_daily_sess.csv' with CSV HEADER

There are other improvements you might consider.

Additional discussion

Randomized name for temporary table

It's not likely you'll run into table-name collisions but, just the same, avoiding them is quite simple by creating a random name...

SELECT 'tmp_' || (RANDOM()*1e15)::INT8 AS table_name
\gset

... and using the name like this.

CREATE TEMP TABLE ":table_name" AS
   [...query...]
\copy ":table_name" to 'ELA_comp_tot_daily_sess.csv' with CSV HEADER

Note the following about the psql features used here.

  • The \gset meta-command runs the query in the buffer and sets non-null values as variables named like the column aliases. For more information, look for \gset here. Importantly, note that the query does not end with a semicolon, as this would immediately execute the buffer!

  • Like all psql variables, the variables created by \gset are used by pre-pending a colon to the variable name, like :table_name.

  • The value of RANDOM()... should be CAST/converted to an INT in order to eliminate any decimal point, which would be troublesome in an unquoted table name and possibly confusing even when quoted. Here, I use a BIGINT (or the alias INT8) because of the large factor I'm multiplying by.

All that said, it's unlikely you are creating enough temporary tables to create conflicting names.

Dynamic filename for output

As long as you're using \gset to create a table-name, you might consider creating a dynamic filename for output.

SELECT 'tmp_' || (RANDOM()*1e15)::INT8 AS table_name,
       'ELA_comp_tot_' || to_char(CURRENT_TIMESTAMP, 'YYYYMMDD_HHMMSS') || '.csv' AS file_name
\gset

Now, you can use the following.

\copy ":table_name" to ":file_name" with CSV HEADER

As written, this would output to something like ELA_comp_tot_20180404_142329.csv. The .csv extension doesn't alter the output, by the way, but better indicates the file contents, particularly to GUI based filesystem interfaces.

Syntax of \copy meta-command

Your sample code (which I've copied verbatim to avoid confusion), is using a COPY command syntax from prior to version 9.0, as mentioned here. Using the newer syntax, it would look like this:

\COPY ":table_name" TO ":file_name" WITH (FORMAT CSV, HEADER TRUE)

This is a minor point, as the syntax is still officially supported through version 9.x. It also doesn't matter that I like to capitalize KEYWORDS. ;-)

Upvotes: 5

ruakh
ruakh

Reputation: 183582

The problem is not that you have line-breaks in your query file, but that you have line-breaks inside the \copy meta-command. According to the "Meta-Commands" section of the psql documentation for PostgreSQL 9.5:

Parsing for arguments stops at the end of the line, or when another unquoted backslash is found. An unquoted backslash is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line.

(emphasis mine). This would be equally true if you were running this at the interactive psql prompt.

One workaround might be to create a temporary function consisting of your query, and having your \copy meta-command run a query that just calls that function.

Upvotes: 4

Related Questions