How to import CSV data to a Postgres table from a string?

Question

I read this solution How to import CSV file data into a PostgreSQL table? that seems to be fine if you want to load data from a file.

I download CSV data from an API endpoint and would like to avoid saving to a file if possible.

I have a NodeJS app that runs this query.

So, can I somehow pass not a file path, but, e.g. a string with the content to the query?

Something like this:

COPY zip_codes FROM 'john,doe,1982-02-01' WITH (FORMAT csv);

Answer 1

from stdin I suppose:

f=# create table aa(a text, b text, c date);
CREATE TABLE
f=# copy aa from stdin delimiter ',';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> john,doe,1982-02-01
>> \.
f=# select * from aa;
  a   |  b  |     c
------+-----+------------
 john | doe | 1982-02-01
(1 row)

update

as you reveal node.js, you are probably looking for https://github.com/brianc/node-pg-copy-streams

here is some example:

js:

client.connect()
var copyFrom = require('pg-copy-streams').from;

var stream = client.query(copyFrom("COPY aa FROM STDIN DELIMITER ','"));
stream.write("john,doe,2017-02-01\n");
stream.end();
var queryresult = client.query('select * from aa', function(err,res) {
  console.log(err,res.rows);
  client.end();
});

output:

C:\Users\Vao\vatest>node t.js
null [ anonymous { a: 'john', b: 'doe', c: 2017-02-01T00:00:00.000Z } ]

sql:

f=# select * from aa;
  a   |  b  |     c
------+-----+------------
 john | doe | 2017-02-01
(1 row)