Hey everyone! I’m working on a bash script and I’ve run into a bit of a problem. I want to use a SQL query that’s stored in a separate file with psql’s \copy command. I’m doing this for testing and to keep things consistent.
Here’s what I’m trying to do:
psql -c my_database "\copy (contents_of_query_file.sql) TO './output.csv' WITH CSV"
The query file (query_file.sql) has something like this:
SELECT city_name FROM cities;
Does anyone know if this is possible? If so, how can I make it work? I’d really appreciate any help or suggestions. Thanks in advance!
yo finn, i think i got a trick for ya. try using a subshell to read the file contents:
psql -d my_database -c “\copy ($(cat query_file.sql)) TO ‘./output.csv’ WITH CSV”
this way, the query gets inserted right into the \copy command. lemme know if it works for ya!
I’ve come across this challenge before and found a solution that separates executing the file and performing the data export. One approach involves using the psql meta-command \i to first execute the SQL file, then running a separate \copy command that utilizes the executed results. For example:
psql -d my_database -c “\i query_file.sql” -c “\copy (SELECT * FROM query_result) TO ‘./output.csv’ WITH CSV”
Ensure that your SQL file is properly formatted, with each statement ending in a semicolon. This method keeps your queries managed in external files while still allowing for data export using \copy.
hey finn! have you tried using the -f option with psql? it lets you run sql from a file. maybe somethin like this:
psql -d my_database -f query_file.sql -c “\copy (SELECT * FROM query_result) TO ‘./output.csv’ WITH CSV”
what do you think? could that work for your script? let me know if you need more help!