From: | Dominique Devienne <ddevienne(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | COPY TO STDOUT WITH (FORMAT CSV, HEADER), and embedded newlines |
Date: | 2022-03-11 17:56:27 |
Message-ID: | CAFCRh--m-2RWTWYxRT=Zjty8Q_WaZWKr5v6ft4z8m5g6Uw_uWg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi.
It's my first time using COPY TO. And first time using built-in CSV support.
Performs well. BUT...
The code below (real code, but using a custom libpq wrapper lib) is run on
a few tables, with unit tests that verify the number of lines of the
output file.
And for a few of those tables, there's a mismatch, the output from PostgreSQL
"has too many lines". I've tracked these to text values in the DB with embedded
newlines. These values are 'normal'. I'm not use to CSV, but I suppose
such newlines
must be encoded, perhaps as \n, since AFAIK CSV needs to be 1 line per row, no?
So how's one supposed to configure the CSV output for the DB with
embedded newlines?
Thanks, --DD
auto rset = my_exec(*conn_, "COPY MY_TAB TO STDOUT WITH (FORMAT
CSV, HEADER)");
if (rset.status() != PGRES_COPY_OUT) {
raise("CSV Export via SQL COPY error: ", rset.error_msg());
}
std::ofstream os(file_name);
bool done = false;
while (!done) {
auto buf = pq::CopyOutBuffer::get(*conn_);
switch (buf.status()) {
case pq::CopyOutStatus::eWait: assert(false); continue;
case pq::CopyOutStatus::eDone: done = true; continue;
case pq::CopyOutStatus::eError: raise("PQgetCopyData: {}",
conn_->error_msg());
case pq::CopyOutStatus::eData: break; // process it below
}
// Each buffer seems to a single line of output,
// with Unix-newline at the end, on all platforms.
os.write(buf.data(), buf.size());
}
os.close();
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2022-03-11 18:28:22 | Re: COPY TO STDOUT WITH (FORMAT CSV, HEADER), and embedded newlines |
Previous Message | Durumdara | 2022-03-11 16:24:27 | Re: Am I in the same transaction block in complex PLPGSQL? |