How to prevent mangling when using COPY FROM PROGRAM

All this time we've been using COPY FROM PROGRAM without any additional adornments. Then we noticed for some things like directory listings in Microsoft windows, it would come across a sequence of inputs it mangled or you may even get an error such as ERROR: end-of-copy marker corrupt.

The trick to get around the issue is to use the FORMAT option. The default FORMAT for COPY FROM PROGRAM is 'text', which tries to escape out some things and in doing so it misinterprets windows slashes so you get weird stuff or no stuff at all.

Case in point, if you do a folder recurse like this:

DROP TABLE IF EXISTS dir_list;
CREATE TABLE dir_list(file_name text);
COPY dir_list FROM PROGRAM 'dir C:\gisdata /b /S'

You may end up with strange symbols in your table or the error: ERROR: end-of-copy marker corrupt

Replace your SQL statement with this:

DROP TABLE IF EXISTS dir_list;
CREATE TABLE dir_list(file_name text);
COPY dir_list FROM PROGRAM 'dir C:\gisdata /b /S'  WITH (format 'csv')

And you'll get something like this:

Query returned successfully: 1449 rows affected, 20 msec execution time. and if you inspect your table:

SELECT file_name 
    FROM dir_list 
    ORDER BY file_name LIMIT 10;

each file is a separate row as it should be:

C:\gisdata\boundaries.zip
C:\gisdata\BOUNDARY_ARC.dbf
C:\gisdata\BOUNDARY_ARC.prj
C:\gisdata\BOUNDARY_ARC.sbn
C:\gisdata\BOUNDARY_ARC.sbx
C:\gisdata\BOUNDARY_ARC.shp
C:\gisdata\BOUNDARY_ARC.shp.xml
C:\gisdata\BOUNDARY_ARC.shx
C:\gisdata\BOUNDARY_POLY.dbf
C:\gisdata\BOUNDARY_POLY.prj