Giter Club home page Giter Club logo

Comments (9)

jeromew avatar jeromew commented on August 12, 2024 1

Check https://www.postgresql.org/docs/9.2/static/sql-copy.html for documentation of the COPY command. Default delimiter is tab so you should be ok.

you problem probably comes from the fact that the last line should not terminate with a newline (otherwise, postgres thinks you want to add one more row, and is lost)

this is wrong :

 var rowText = arr.join('\t') + '\n'

because it always add a '\n'

var rowText = '\n' + arr.join('\t')

would be better, but not for the first line where the '\n' should not be added

from node-pg-copy-streams.

jeromew avatar jeromew commented on August 12, 2024 1

I think there is a mismatch of things in your approach.
you should enter 'through2.obj' with row level data.

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

        interndataset.forEach(function(r) {
          internmap.write(r);
        })
        internmap.end();

then make sure your table has 2 columns.

from node-pg-copy-streams.

matt212 avatar matt212 commented on August 12, 2024

Hi @jeronew,
thanks for heads up i was referring to old writable repo.
https://github.com/brianc/node-pg-writable/blob/master/index.js
i am quiet nascent in terms of modifying stream and also Copy command
Also even if i remove \n from below codebase:-
var rowText = arr.join('\t') + '\n'
to
var rowText = arr.join('\t')
the chunks from on data callback from internmap.pipe(stream) is below and it is not getting inserted into database
copy issues
Any pointers on same !

from node-pg-copy-streams.

jeromew avatar jeromew commented on August 12, 2024

you need the '\n' between each rows.

from node-pg-copy-streams.

matt212 avatar matt212 commented on August 12, 2024

hi @jeromew ,
extremely sorry missed your post content with newline '\n'
also even after appending newline first
var rowText = '\n' + arr.join('\t')
nothing is happening
I think there is some issue with my below code

internmap.write(data);
internmap.pipe(stream);

or data
data

my overall code snippet

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

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


console.log(data);
var internmap = through2.obj(function(arr, enc, cb) {
    var rowText = '\n' + arr.join('\t')

    cb(null, rowText)
})
internmap.write(data)


internmap.pipe(stream);

stream.on('finish', function() {
    resolve(0);


})
stream.on('error', function(err) {
    console.log(err)
    resolve(0);

})
internmap.on('finish', function() {
    resolve(0);


})
internmap.on('data', function(chunk) {

    console.log(chunk);

})

internmap.on('error', function(err) {
    console.log(err)
    resolve(0);
})

})
})
}

any pointers
thanks for patience !

from node-pg-copy-streams.

jeromew avatar jeromew commented on August 12, 2024

should be something like

var started = false;
var internmap = through2.obj(function(arr, enc, cb) {
   var rowText = (started ? '\n' : '') + arr.join('\t');
   started = true;
   cb(null, rowText);
})
  • maybe remove
    internmap.on('data', function(chunk) {
    console.log(chunk);
    })

because this sometimes is greedy and consumes data before the stream pipeline is operational (cf documentation on flowing vs non-flowing streams)

  • also where do you end the stream ?

    internmap.write(data)

vs

internmap.write(data)
internmap.end()

is the stream is not ended, you cannot be sure data is flushed.

from node-pg-copy-streams.

matt212 avatar matt212 commented on August 12, 2024

Hi @jeromew
thank you so much i forgot to flush after write !
however , when i do that, I get below error
geez

updated code snippet with sample dataset

var sqlcopysyntax = 'COPY intern(starttime) FROM STDIN ';

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

            console.log(sqlcopysyntax)


            var interndataset = [
                ['0.382'],
                ['5.918'],
                ['19.554'],
                ['24.151'],
                ['25.507'],
                ['27.734'],
                ['27.734'],
                ['29.336'],
                ['31.71'],
            ];

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

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

            internmap.pipe(stream);


            internmap.on('data', function(chunk) {
                console.log(chunk);
            })

            stream.on('finish', function() {
                resolve(0);


            })
            stream.on('error', function(err) {
                console.log(err)
                resolve(0);

            })


            internmap.on('finish', function() {
                resolve(0);


            })


            internmap.on('error', function(err) {
                console.log(err)
                resolve(0);
            })

my postgres table ddl

CREATE TABLE public.intern (
	starttime varchar NULL
)
WITH (
	OIDS=FALSE
) ;

i think i might have some issues with array interndataset , since now it is single column moving forward it would multiple columns with ofcourse copy statement to two columns

 var interndataset = [
                ['0.382','5.918'],
                ['5.918','5.918'],
                ['19.554','5.918'],
                ['24.151','5.918'],
                ['25.507','5.918'],
                ['27.734','5.918'],
                ['27.734','5.918'],
                ['29.336','5.918'],
                ['31.71','5.918'],
            ];

any pointers

from node-pg-copy-streams.

matt212 avatar matt212 commented on August 12, 2024

Hi @jeromew ,
got it working for single column entry ,now stuck up with multiple column entry

necessary modification in code snippets!

from
var rowText = (started ? '\n' : '') + arr.join('\t');
to this
var rowText = '\n' + arr.map( function( item ){ return ('\n' +item.join('\t')) } ).join('');

this works for single column inserts, but working on multi column insert like for
e.g -

var interndataset = [
    ['1', '4'],
    ['6', '12.074'],
    ['13.138', '16.183'],
    ['17.226', '21.605'],
    ['22.606', '24.733'],
    ['24.816', '27.027'],
];

gives


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

var sqlcopysyntax = 'COPY srt (starttime, endtime) FROM STDIN with delimiter \t ';
gives
error: syntax error at end of input
and below
var sqlcopysyntax = 'COPY srt (starttime, endtime) FROM STDIN with delimiter \'\\t\' ';
gives
error: COPY delimiter must be a single one-byte character

when i place

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

instead of this

var sqlcopysyntax = 'COPY intern(starttime) FROM STDIN ';

but getting below error
laststage

Any pointers on how to modify for more than one columns.
Any delimiter setting or code snippets changes ?

Thanks for your understanding and patience !

from node-pg-copy-streams.

matt212 avatar matt212 commented on August 12, 2024

hi @jeromew ,
you sir are lifesaver !

from node-pg-copy-streams.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.