CODEWITHSUNDEEP
postgresqlcsvexport-to-csvpsql
Michael Johnson
Michael Johnson

Reputation: 3

Why does my postgresql csv export have more rows than the table?

I am trying to copy a table in a postgresql database (version 10.12) via psql. One of the rows contains strings representing xml data. When I query the database for a row count with this query I get a count of about 50,000:

select count(column) from table;

But when I try to export the data to a csv file the output has more than 1,000,000 rows! I don't understand how a csv export could have a different number of rows than the table!

This is the copy command:

\copy (select column from table) to 'directory/output.csv' with csv;

It doesn't seem to matter if I change the delimiter or quote either. I've tried using | as a delimiter and ` as a quote and the number of rows in the csv was the same. Why is the row count different in the csv export?

Upvotes: 0

Views: 1114

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61656

The row count is not different: the CSV output simply has linefeeds (LF, ASCII code 10) embedded in fields, which is expected in XML.

If you want one line per row with COPY, don't use CSV, use the text format, that is, just omit with csv. Then newlines are encoded with \n instead of being output verbatim.

Upvotes: 1

Related Questions