Escaping Backslash using Knex.raw for \Copy command in Postgres

Question

We were trying to use the \COPY command in Knex.raw. We connect via heroku/postgres and the \COPY is the only way to bulk import data as we don't have superuser rights. The backslash before the copy is being removed or multiplied depending on what you try.

So far we've tried to escape it using 1. \b 2. \ 3. String.fromCharCode(92) 4. concatenation

The full function we're using is...

knex.raw("\COPY tablename (column1, column2, ...columnN) FROM STDIN " + csvValues).then(function(response){
console.log('the response from the copy', response);

});

We've tested this exact function within our pg:psql environment and it works perfectly. If you know of a better way of doing this, please let us know!

Regards, Brandon

Answer 1

\copy is a psql (client) command, which wraps Postgres's COPY (server). It sounds like perhaps you're trying to combine the two, and that's where the problem is.

Since knex.js is a query builder, it wouldn't know about \copy -- \COPY would not be valid SQL to use in a client scenario.

You mention that you don't have superuser access, which, if you want to reference a file, is required for COPY.

If you're unable to use COPY for that reason, you would instead need to use psql and leverage its \copy command, for which knex.js would likely not be the tool to use. Rather, you would need to access it via the invocation of a shell command.

Perhaps this batch import of data could be done outside of Javascript entirely? i.e. via a bash script that used psql directly?