How to seed data into a postgres database table from a .csv file?

Question

I am trying to seed data into a psql table from a .csv file. This is the setup:

data.csv:
name,price,section Aluminum Foil,8.84,miscellaneous Apples,10.81,produce

I am using the pg module in Node to connect to database grocery_store that has 1 table grocery_items with columns id, name, price and section.

const Client = require('pg').Client
const connectionString = 'postgresql://localhost:5432/grocery_store'
const pg = new Client( { connectionString: connectionString } )

How can I now seed data from data.csv into the grocery_items table?

I've tried the pg-copy-streams module and they suggest to do:

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

pg.connect(function(err, client, done) {
  var stream = client.query(copyFrom('COPY my_table FROM STDIN'));
  var fileStream = fs.createReadStream('some_file.tsv')
  fileStream.on('error', done);
  fileStream.pipe(stream).on('finish', done).on('error', done);
});

But I got pg.connect is not a function error when I tried this.

@baibhavx did you get this to work? I am curious about the same question. — Mihir Patel
– Mihir Patel, Commented Dec 13, 2017 at 15:28
@MihirPatel \copy grocery_items(name, price, section) FROM './data.csv' DELIMITER ',' CSV HEADER; solved my problem! — baibhavx
– baibhavx, Commented Jan 5, 2018 at 3:31

Shifat · Accepted Answer · 2017-09-24 21:39:40Z

7

It should work fine.

Create your table:

CREATE TABLE my_table 
(name varchar(50), price int, section varchar(50));

Copy data from your CSV file to the table:

COPY my_table FROM '/path/to/csv/my_table.txt' WITH (FORMAT csv);

answered Sep 24, 2017 at 21:39

Shifat

7601 gold badge7 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to seed data into a postgres database table from a .csv file?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related