CODEWITHSUNDEEP
sqlpostgresqlherokucloudbulk-load
Ahmad Mujtaba
Ahmad Mujtaba

Reputation: 105

How to insert Bulk data from csv in Postgres-Heroku?

I have connected my hosted postgres-db from heroku to my local machine, using dbeaver, connection was successful, and when I do try to insert single or multiple data(more than one but not bulk) at a time it does, but when I do try to insert data in bulk from CSV file it fails, and giving the following error:

SQL Error [42501]: ERROR: must be superuser or have privileges of the pg_read_server_files role to COPY from a file
  Hint: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone

Data somehow looks like this:

,title,ingredients,directions,link,source,NER
0,No-Bake Nut Cookies,"[""1 c. firmly packed brown sugar"", ""1/2 c. evaporated milk"", ""1/2 tsp. vanilla"", ""1/2 c. broken nuts (pecans)"", ""2 Tbsp. butter or margarine"", ""3 1/2 c. bite size shredded rice biscuits""]","[""In a heavy 2-quart saucepan, mix brown sugar, nuts, evaporated milk and butter or margarine."", ""Stir over medium heat until mixture bubbles all over top."", ""Boil and stir 5 minutes more. Take off heat."", ""Stir in vanilla and cereal; mix well."", ""Using 2 teaspoons, drop and shape into 30 clusters on wax paper."", ""Let stand until firm, about 30 minutes.""]",www.cookbooks.com/Recipe-Details.aspx?id=44874,Gathered,"[""brown sugar"", ""milk"", ""vanilla"", ""nuts"", ""butter"", ""bite size shredded rice biscuits""]"
1,Jewell Ball'S Chicken,"[""1 small jar chipped beef, cut up"", ""4 boned chicken breasts"", ""1 can cream of mushroom soup"", ""1 carton sour cream""]","[""Place chipped beef on bottom of baking dish."", ""Place chicken on top of beef."", ""Mix soup and cream together; pour over chicken. Bake, uncovered, at 275\u00b0 for 3 hours.""]",www.cookbooks.com/Recipe-Details.aspx?id=699419,Gathered,"[""beef"", ""chicken breasts"", ""cream of mushroom soup"", ""sour cream""]"
2,Creamy Corn,"[""2 (16 oz.) pkg. frozen corn"", ""1 (8 oz.) pkg. cream cheese, cubed"", ""1/3 c. butter, cubed"", ""1/2 tsp. garlic powder"", ""1/2 tsp. salt"", ""1/4 tsp. pepper""]","[""In a slow cooker, combine all ingredients. Cover and cook on low for 4 hours or until heated through and cheese is melted. Stir well before serving. Yields 6 servings.""]",www.cookbooks.com/Recipe-Details.aspx?id=10570,Gathered,"[""frozen corn"", ""cream cheese"", ""butter"", ""garlic powder"", ""salt"", ""pepper""]"

what I tried:

I have checked this query on my local Postgres instance it works

Query works well on local postgres instance i.e localhost:5432

data is around 2M+

data is in text format only

database is owned by me, it is a basic postgres instance of heroku

PSQL script:

CREATE TABLE nlg_recipe (
    title VARCHAR(255) NULL,
    ingredients TEXT NULL,
    directions TEXT NULL,
    link TEXT,
    source VARCHAR(100),
    NER text NULL
);
-- not working
COPY nlg_recipe FROM '/Users/Container/data_sets/nlg_recipe_dataset.csv' DELIMITER ',' CSV HEADER;
-- working
INSERT INTO nlg_recipe (title, ingredients, directions, link, source, NER)
VALUES 
('Chicken Alfredo Pasta', 
 '250g fettuccine pasta, 1 cup cooked chicken, 1 cup heavy cream, 1/2 cup grated Parmesan cheese, salt and pepper to taste', 
 '1. Cook pasta according to package instructions. 2. In a saucepan, combine heavy cream and Parmesan cheese. 3. Add cooked chicken and season with salt and pepper. 4. Toss the cooked pasta in the sauce. 5. Garnish with extra Parmesan and serve.', 
 'https://example.com/chicken-alfredo-pasta', 
 'Allrecipes', 
 'Chicken Alfredo, Fettuccine Pasta, Heavy Cream, Parmesan Cheese, Salt, Pepper');

Upvotes: 0

Views: 161

Answers (1)

j.d.o.
j.d.o.

Reputation: 131

Since you have not provided the exact script you are using then here is a generic reply.

The COPY command requires superuser privileges on the machine on which the DB is running on. The psql \copy command runs on your local machine and does not require superuser privileges. If you can use psql with the \copy command instead of DBeaver this should solve the problem.

If you have to use DBeaver then there is a import wizard you can use. Find the import wizard like so: In the left hand DB navigator navigate to the table into which you want to import data. Right-click on the table, choose "Import Data", then follow the wizard.

Edit based on the edited question:

All you have to do then is to add a backslash before your COPY command - if you run it from psql. COPY nlg_recipe FROM... becomes \COPY nlg_recipe FROM...

Full command to be run in psql:

\COPY nlg_recipe FROM '/Users/Container/data_sets/nlg_recipe_dataset.csv' DELIMITER ',' CSV HEADER;

Upvotes: 0

Related Questions