How do I replace (select current_timestamp) with a filename that houses this same select statement?

Question

I am using PSQL. My command line is:

$\copy (select current_timestamp) to '/home/myname/outputfile.txt'

I would like to know, How do I replace "(select current_Timestamp)" with a filename that houses that same select statement?

ex:

$\copy (My_SQL_FILE.sql) to '/home/myname/outputfile.txt'

I've tried googling but I can't seem to find the answer.

$\copy (my_Sql_file.sql) to '/home/myname/outputfile.txt'

does not work

score 0 · Answer 1 · answered Apr 22 '19 at 09:54

You want to run a query stored in a file in a \copy statement, i.e. execute that query and store the output in a file? That is doable.

I've come across this use-case myself and wrote psql2csv. It takes the same arguments as psql and additionally takes a query as parameter (or through STDIN) and prints the output to STDOUT.

Thus, you could use it as follows:

$ psql2csv [CONNECTION_OPTIONS] < My_SQL_FILE.sql > /home/myname/outputfile.txt

What psql2csv will basically do is transform your query to

COPY ($YOUR_QUERY) TO STDOUT WITH (FORMAT csv, ENCODING 'UTF8', HEADER true)

and execute that through psql. Note that you can customize a bunch of things, such as headers, separator, etc.

How do I replace (select current_timestamp) with a filename that houses this same select statement?

1 Answers1