khajlk
I am using PostgreSQL 11 at Windows 10 (x64) based machine. I am trying to write some data from my postgres table to a CSV file.
```
COPY (select
col1, --numeric format
col2, --numeric format
col3 --text/character string
from
my_table
order by my_table_id
) To 'my_path/my_file.csv'
with csv delimiter ',' encoding 'utf-8';
```
I get the output like:
```
31,2,"2,211,1,27.3,1,31,0.0"
```
I would like to turn off the qoutes while writing to the CSV, and expect the output like:
```
31,2,2,211,1,27.3,1,31,0.0
```
I have tried to turn off quotes in PostgreSQL COPY command but no success. Any help would be highly appreciated. Alternatively, can it also be possible to convert the text string to numeric array in query and then write to CSV?
Top Answer
PeterVandivier
One option might be to tell psql to make the `QUOTE` character a space `' '`, although you should be aware of the risk of creating a malformed csv.
```sql
COPY (select
col1, --numeric format
col2, --numeric format
col3 --text/character string
from
my_table
order by my_table_id
) To 'my_path/my_file.csv'
with csv delimiter ',' encoding 'utf-8' quote ' ';
```
Note if we add an additional row with a less tidy comma series, an application like excel might now read it in an unexpected way.
<>https://dbfiddle.uk/?rdbms=postgres_12&fiddle=fa7943806dc274eb1bbe794979459489
Note that `psql` is trying to protect you here by communicating that the "2,211,1,27.3,1,31,0.0" data is a single scalar value in the database. If you want to export a csv without trickery, you need to properly convert the single scalar value into something the database recognises as a series of columns. the complexity of this will scale if other values in that column have a different number or pattern of commas.
If you have the liberty of knowing that your comma-delimited string follows strict patterns, you can split it into an array and index into it in perhaps the following way.
<>https://dbfiddle.uk/?rdbms=postgres_12&fiddle=9060ea63946fae2cffc7e39811b483d8&hide=1
It's clear in this case though that we'd lose values passed the 10th record in any subsequent delimited strings.