Shell Script To Write PostgreSQL SQL Query Results In To CSV Format

Question

I need to create a Linux shell script to run a PostgreSQL SQL query and export the results in CSV Format. By doing some research I came to know there are two ways I can do it such as below:

using an SQL query

COPY table_name TO ‘file_name.csv' DELIMITER ',' CSV HEADER;

using psql

\copy table_name to 'filename.csv’ csv header

I have tried both from SQL server out of which I found the first method needs administrative permission, which I don't want.

I have tried the second method and am able to write the PostgreSQL query output to a CSV file as well, but now I need to do that from a shell script, where I am facing the below issue. I am writing inside the script as follows:

psql -U username -d database -c $'
\copy(WITH var(collectionMonth) as (values(\'$collectionMonth\'))
SELECT CONCAT(var.collectionMonth, LPAD(mfv2.integer_value::text,2,\'0\')) as "collection_date" FROM var,
meta_field_value mfv2 WHERE mfv2.meta_field_name_id=8 TO \'/home/my.csv\' csv HEADER;'

It shows “syntax error near \copy”. How to fix this? I know the error is because of the \ in front of the copy command.

Laurenz Albe · Accepted Answer · 2019-05-29 10:32:45Z

2

Your quoting is off.

Best is to use a “here document”:

psql -U username -d database <<EOF
\copy (WITH var(collectionMonth) as (values('$collectionMonth')) SELECT CONCAT(var.collectionMonth, LPAD(mfv2.integer_value::text,2,'0')) as "collection_date" FROM var, meta_field_value mfv2 WHERE mfv2.meta_field_name_id=8) TO '/home/my.csv' (FORMAT 'csv', HEADER)
EOF

answered May 29, 2019 at 10:32

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

abhisek Over a year ago

with using EOF also I am getting error "invalid command \copy("

Abelisto Over a year ago

@abhisek insert space between \copy and ( as shown above.

abhisek Over a year ago

@Abelisto , I got it now. It's not the space issue . for \copy the complete command should be in one line.

Collectives™ on Stack Overflow

Shell Script To Write PostgreSQL SQL Query Results In To CSV Format

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related