Devart Forums

sideshowleonard

Can we resurrect this topic?

We know that "copy from stdin" is more performant when *loading* data:
http://www.postgresql.org/docs/9.2/static/populate.html

.."copy from stdin" would have been nice for a database conversion project I was working on several years ago, which migrated an old Access database to PostgreSQL.

There is also an interesting case for *dumping* data with "copy to stdout," even though the performance difference (as previously stated) isn't compelling...

Postgresql has already gone through the pains of writing/reading CSV format (http://secretgeek.net/csv_trouble). For example, what if your field contains a carriage return/line feed? Did you escape it properly on output? Did you parse it properly in input? Instead of relying on a CSV output library, or writing our own, which may be buggy, it would be nice to write a query like:

Code: Select all

COPY (
  SELECT
      student.name AS "Student Name"
    , score.grade AS "Grade"
    , course.name AS "Course Name"
    , student.notes AS "Student Notes"
  FROM 
         student
    JOIN score ON student.id=score.student_id
    JOIN course ON score.course_id=course.id
  WHERE
        score.semester='2014 S2'
    AND course.name='SQL 101'
) TO STDOUT WITH CSV HEADER;

..and then read that result from the query object and write it to a file (one that, for example, could be digested by a csv reporting tool.. or MS Excel.. or bulk-imported into another database.. etc...).

If necessary, I'm sure I can come up with more reasons why 'copy to stdout' is good to have.

Cheers,
-Leo

Devart Forums

Search found 1 match

Re: PostgreSQL COPY TO command available?