multiple column copy format postgresql Node.js

问题:

I using postgres stream to insert record into postgres , for single column works fine , but what is ideal data format for copy for multiple columns

code snippets

  var sqlcopysyntax = 'COPY srt (starttime, endtime) FROM STDIN  delimiters E\'\\t\'';

        var stream = client.query(copyFrom(sqlcopysyntax));

        console.log(sqlcopysyntax)


        var interndataset = [
            ['1', '4'],
            ['6', '12.074'],
            ['13.138', '16.183'],
            ['17.226', '21.605'],
            ['22.606', '24.733'],
            ['24.816', '27.027'],
            ['31.657', '33.617'],
            ['34.66', '37.204'],
            ['37.287', '38.58'],
            ['39.456', '43.669'],
            ['43.752', '47.297'],
            ['47.381', '49.55'],


        ];

        var started = false;
        var internmap = through2.obj(function(arr, enc, cb) {
/* updated this part by solution provided by @VaoTsun */            
var rowText = arr.map(function(item) { return (item.join('\t') + '\n') }).join('') 
                started = true;
                //console.log(rowText)
                rowText=rowText+'\\\.';
 /* end here*/
            started = true;

            cb(null, rowText);
        })

        internmap.write(interndataset);
        internmap.end();

        internmap.pipe(stream);

wherein i got error: (due to delimiter)missing data for column "endtime"(resolved) but got below error

error: end-of-copy marker corrupt

COPY intern (starttime, endtime) FROM STDIN
1                       4
6                       12.074
13.138                  16.183
17.226                  21.605
22.606                  24.733
24.816                  27.027
31.657                  33.617
34.66                   37.204
37.287                  38.58
39.456                  43.669
43.752                  47.297
47.381                  49.55

any pointer on how to resolve this . what would be ideal format for multiple column inserts using copy command

回答1:

With immense help from @jeromew from github community.

and proper implementation of node-pg-copy-streams(takes away copy command complexity ). we were able to solve this issue

https://github.com/brianc/node-pg-copy-streams/issues/65 below is working code snippets

var sqlcopysyntax = 'COPY srt (starttime, endtime) FROM STDIN  ;

    var stream = client.query(copyFrom(sqlcopysyntax));

    console.log(sqlcopysyntax)


    var interndataset = [
        ['1', '4'],
        ['6', '12.074'],
        ['13.138', '16.183'],
        ['17.226', '21.605'],
        ['22.606', '24.733'],
        ['24.816', '27.027'],
        ['31.657', '33.617'],
        ['34.66', '37.204'],
        ['37.287', '38.58'],
        ['39.456', '43.669'],
        ['43.752', '47.297'],
        ['47.381', '49.55'],


    ];

    var started = false;
        var internmap = through2.obj(function(arr, enc, cb) {
            var rowText = (started ? '\n' : '') + arr.join('\t');
            started = true;

            cb(null, rowText);
        })

        data.forEach(function(r) {
            internmap.write(r);
        })

    internmap.end();

    internmap.pipe(stream);

回答2:

https://www.postgresql.org/docs/current/static/sql-copy.html

DELIMITER

Specifies the character that separates columns within each row (line) of the file. The default is a tab character in text format, a comma in CSV format. This must be a single one-byte character. This option is not allowed when using binary format.

try using not default delimiter (as tabulation can be replaced on copy/paste), eg:

t=# create table intern(starttime float,endtime float);
CREATE TABLE
t=# \! cat 1
COPY intern(starttime,endtime) FROM STDIN delimiter ';';
1;4
6;12.074
13.138;16.183
17.226;21.605
22.606;24.733
24.816;27.027
31.657;33.617
34.66;37.204
37.287;38.58
39.456;43.669
43.752;47.297
47.381;49.55
49.633;54.68
54.763;58.225
59.142;62.98
64.189;68.861
69.82;71.613
72.364;76.201
76.285;78.787
78.871;81.832
\.

t=# \i 1
COPY 20

Also in your question you lack \., try typing in psql - you will see instructions:

t=# COPY intern(starttime,endtime) FROM STDIN delimiter ';';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.

End with a backslash and a period on a line by itself.