Postgresql COPY command to export data to csv : Can we achieve double quotes for strings in csv's?

user_11120152 0
Viewed 457

I'm trying to export data from table to csv using COPY command. Here is my code

String copyQuery="COPY "+tableName+ " TO STDOUT WITH DELIMITER '~' CSV HEADER";
        System.out.println(copyQuery);
        try {
            FileWriter fw=new FileWriter(FilePath);
                 new CopyManager((BaseConnection) conPost)
                    .copyOut(
                        copyQuery, fw
                        );
                 fw.close();
                 logger.info("postgres side csv generated");
            }

When I look into CSV the output is like:- 1~vignesh~java

But, I prefer the strings to be in double quotes :- 1~"vignesh"~"java"

How can I achieve this??????

Thanks in advance

postgresql sql
1 Answers

Use FORCE QUOTE and list the columns you want to wrap with double quotes:

COPY t TO STDOUT WITH CSV HEADER DELIMITER '~' FORCE QUOTE colum2,colum3..;

.. or * to wrap all columns:

COPY t TO STDOUT WITH CSV HEADER DELIMITER '~' FORCE QUOTE *;

Demo:

CREATE TABLE t (a int, b text, c text);
INSERT INTO t VALUES (1,'vignesh','java');

Wrapping the columns b and c:

COPY t TO STDOUT WITH CSV HEADER DELIMITER '~' FORCE QUOTE b,c;

a~b~c
1~"vignesh"~"java"

Wrapping all columns:

COPY t TO STDOUT WITH CSV HEADER DELIMITER '~' FORCE QUOTE *;

a~b~c
"1"~"vignesh"~"java"
user_22753880
Related