Line breaks in psql query file possible?

Question

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

Answer 1

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