CODEWITHSUNDEEP
sqlpostgresqlcsv
anvd
anvd

Reputation: 4047

Update new column with data from csv file

I have a postgres table called users_user with a new, and empty column, called uuid_unsubscribe.

Now I have a csv file with 420 uuids, one for each user.

uuids.csv

e0af0a31-3eb3-43d5-9d3d-xxx
e0af0a31-3eb3-43d5-9d3d-yyy
e0af0a31-3eb3-43d5-9d3d-zzz
....

My question is, how to update this table, specifically add a new uuid for each user from that file? I want to avoid one manual update for each row.

Upvotes: 1

Views: 390

Answers (1)

rd_nielsen
rd_nielsen

Reputation: 2459

You'll need to import the CSV file of UUID values and assign each row a unique identifier that can be matched with the primary key of your users_user table, then update users_user from the joined imported table.

Here's one approach to do that.

Import the CSV file--assume that the imported file is named imported_uuids and the column containing the UUID values is named uuid.

Add a temporary sequence column to the users_user table.

alter table users_user
add column seq bigint;

update users_user as a
set seq = rn
from (select user_key_column, row_number() over() as rn from users_user) as x
where x.user_key_column=a.user_key_column;

Assign a similar set of seq values to the imported UUID values and run the update:

update users_user as a
set uuid_unsubscribe = n.uuid
from (select row_number() over() as seq, uuid from imported_uuids) as n
where n.seq = a.seq;

Clean up.

alter table users_user
drop column seq;

If your users_user table already has a column that contains a 1-based sequence of numeric values without gaps, you could use that instead of adding the seq column as done in this illustration.

Upvotes: 1

Related Questions