CODEWITHSUNDEEP
postgresqlcsv
Danish
Danish

Reputation: 199

import csv file into postgres table based on some key

I want to import one CSV file that is having only two columns as 'name' and 'details'.

In my postgres sql i have a table called 'Person' which is having 8 column including 'name' and 'details'.

Now what i want is to import the csv file into Person table and when the name column of csv matches the Person name column it should import the details column into Person .

That is based on key match and the key is name and value is details.

Is there any other way possible.

Upvotes: 1

Views: 176

Answers (2)

Jim Jones
Jim Jones

Reputation: 19613

I would recommend to create a temporary table and from there populate the person table with a simple update query.

Data Sample

CREATE TABLE public.person(
  name text,
  details text,
  something_else text);

INSERT INTO person (name) VALUES 
('foo'),('bar');

CREATE TABLE public.tmp_person(
  name text,
  details text);

CSV file content:

name, details
foo, det1
bar, det2

Importing the csv file into tmp_person, e.g. via psql:

$ cat file.csv | psql -d mydb -U myuser -c "COPY tmp_person FROM STDIN CSV HEADER"

Then update your person table:

UPDATE person SET details = tmp.details
FROM tmp_person tmp WHERE tmp.name = person.name;

In the end the table person will have the details from the csv file, so you might wanna DROP TABLE tmp_person.

SELECT * FROM person;
 name | details | something_else 
------+---------+----------------
 foo  |  det1   | 
 bar  |  det2   | 
(2 Zeilen)

Upvotes: 0

Hambone
Hambone

Reputation: 16377

If I understand you, your table has > 2 fields, but your CSV file has only two, so like this:

create table person (
  blah
  blah
  name text,
  details text,
  blah
  blah
)

person.csv:

name,details
Hambone,Where Ya Been?
Luke,Skywalker

If this is the case, you can specify the fields in your copy command:

copy person (name, details)
from '/var/tmp/person.csv'
with null as '' csv header;

Upvotes: 0

Related Questions