PostgreSQL, need to update some tables from CSV file

Question

I have a DB with discount card records, i need to copy the info from main DB to other with the same struckture. Need to update the fields name, phone etc from the main DB. The info i need is in three tables: 1.pers_cards (the card numbers). 2.pers_clients (the name, phone, etc). 3 pers_accounts (the table combine two previous table by fields client_id and account_id) I wrote script to get the info i need:

Copy (SELECT pers_cards.card_num, pers_clients.name, pers_clients.address, 
             pers_clients.phone, pers_clients.mobilephone, pers_clients.email, 
             pers_clients.birthday,pers_clients.gender_male
      FROM   pers_cards,pers_accounts,pers_clients 
      WHERE (
          pers_accounts.client_id = pers_clients.client_id 
          and 
          pers_cards.acc_id = pers_accounts.acc_id 
      ))
To 'f:/test.csv' With CSV;

Now i need to update the other BD using this file, update fields with personal info (pers_clients.name, pers_clients.phone etc) depending of its card number. trouble is the fields client_id and account_id has different values in different tables.

CREATE TABLE cards_temp
(card_num varchar, name varchar, address varchar, 
phone varchar, mobilephone varchar, email varchar, birthday date, gender_male bool);

COPY cards_temp FROM 'f:/test.csv' DELIMITER ',' CSV;

UPDATE cards cards
SET name = cards_temp.name
FROM cards_temp
WHERE cards_temp.card_num = cards.card_num;

How to update my main table:

UPDATE pers_clients 
SET name = cards_temp.name
FROM cards_temp
WHERE cards_temp.card_num = (SELECT pers_cards.card_num
FROM pers_cards,pers_accounts,pers_clients 
WHERE (
pers_accounts.client_id = pers_clients.client_id and pers_cards.acc_id = pers_accounts.acc_id ));

Er, no. They share a common subset, SQL, but the command languages are far from the same. If they were, there'd be no point having postgresql and mysql tags. — Craig Ringer
– Craig Ringer, Commented Jan 24, 2014 at 7:43

Craig Ringer · Accepted Answer · 2014-01-24 01:34:47Z

1

You'll want to:

create an UNLOGGED table with the structure of the CSV data
COPY the data to the new unlogged table
Use a series of SQL UPDATE ... FROM statements to update each target table using a join against the unlogged table full of temporary data
Drop the unlogged table

answered Jan 24, 2014 at 1:34

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

Comments

Kostya Stepanyuk · Accepted Answer · 2014-01-25 12:21:34Z

You can JOIN your TEMP table and using fields from it update your main one

UPDATE pers_clients AS clients
SET name = tmp_cards.name, 
    address = tmp_cards.address, 
    phone = tmp_cards.phone,
    mobilephone = tmp_cards.mobilephone,
    email = tmp_cards.email,
    birthday = tmp_cards.birthday,
    gender_male = tmp_cards.gender_male
FROM pers_accounts AS accounts, pers_cards AS cards, cards_temp AS tmp_cards
WHERE accounts.client_id = clients.client_id
AND accounts.acc_id = cards.acc_id 
AND cards.card_num = tmp_cards.card_num

U r my saver, works perfectly, thats what i was looking for. Thank you

Collectives™ on Stack Overflow

PostgreSQL, need to update some tables from CSV file

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related