Update an existing user table after comparing it with new user list on Postgresql database.

Objective
Update existing user table based upon new user list received such that
a. Deactivate users who are not in new list but were old table.
b. Update password of users as per new list.
c. Insert users records who are present only in new list.

We have a user table with fields :

Existing users table is as follows:

id             - bigint auoincrement sequence_generator
login          - varchar
password_hash  - varchar
activated      - boolean

say for example the existing user table data is as follows -

id,login,password_hash,activated
1,Ram,pass1,true
2,Shyam,pass2,true
3,Mohan,pass3,true
4,Sohan,pass4,true

new userlist is as follows-

login,new_password_hash
Ram,pass1
Mohan,newpass3
Sohan,pass4
Sita,pass5
Gita,pass6

After updation the user table should look like this -

id,login,password_hash,activated
1,Ram,pass1,true
2,Shyam,pass2,false
3,Mohan,newpass3,true
4,Sohan,pass4,true
5,Sita,pass5,true
6,Gita,pass6,true

Solution

I have used Postgresql-11 database with table users whose data need to be updated.

1. Create a newtable newusers

CREATE TABLE newusers ( login varchar(50) primary key, new_password_hash varchar(60) );

2. Upload data to the newusers table

\copy newusers (login,new_password_hash) from ‘newlist.csv’ delimiter ‘,’ csv header;

3. Get number of users which are present in old list but not in new list

select count(*) from users where login not in (select login from newusers);
 

4. Deactivate the users which are not present in the new list.

update users set activated = false where login not in (select login from newusers );

5. Verify if all users who were not in newlist has been deactivated.

select count(*) from users where activated = true and login not in (select login from newusers);

6. Count users which are present in both list.

select count(*) from users where login in (select login from newusers);

7. Update password of users which are present in both list

update users set password_hash = newusers.new_password_hash from newusers where users.login=newusers.login;

8. Count users which are present only in the new list

select count(login) from newusers where login not in ( select login from users);

9. create users that are present only in new list

insert into users( id, login, password_hash,activated) 
select nextval(‘sequence_generator’) as id, login, new_password_hash as password_hash, true::boolean as activated from newusers where newusers.login not in ( select login from users);

10. Verify that new users are created.

select count(*) from newusers where login not in ( select login from users);