I need to make something to merge some users in PGSQL but I think that pgsql don't own the MERGE property. I just want to know how to make two users to be matched like this :
id | name | username | mail
1 | toto | tata | [email protected]
2 | titi | tutu | [email protected]
Here I want to chose which data I would like I want to say that I want to merge only username from 2 to 1 so the result would be :
id | name | username | mail
1 | toto | tutu | [email protected]
You just need to select all the columns for first id and the column you need with second id will be a subquery in select list. Please check below answer for selecting merged result.
Schema and insert statements:
create table users (id int , name varchar(50), username varchar(50), mail varchar(50));
insert into users values (1 , 'toto' , 'tata' , '[email protected]');
insert into users values (2 , 'titi' , 'tutu' , '[email protected]');
Query:
select id,name,(select username from users where id=2) username,mail from users where id=1
Output:
| id | name | username | |
|---|---|---|---|
| 1 | toto | tutu | [email protected] |
db<fiddle here
To merge the rows within the table you can first update first row with data from second row then delete the second row. Try this:
Schema and insert statements:
create table users (id int , name varchar(50), username varchar(50), mail varchar(50));
insert into users values (1 , 'toto' , 'tata' , '[email protected]');
insert into users values (2 , 'titi' , 'tutu' , '[email protected]');
Update query:
update users set username=(select username from users where id=2) where id=1;
delete from users where id=2;
Select query:
select * from users
| id | name | username | |
|---|---|---|---|
| 1 | toto | tutu | [email protected] |
db<fiddle here
You could use aggregation:
select min(id) as id,
max(name) filter (where id = 1) as name,
max(username) filter (where id = 2) as username,
max(mail) filter (where id = 1) as mail
from t
where id in (1, 2);
This assumes that you want to pull particular column values from particular ids.
Or you could use join:
select t1.id, t1.name, t2.username, t1.mail
from t t1 join
t t2
on t1.id = 1 and t2.id = 2;
If you actually want to change the data, use update and delete:
update t t1
set username = t2.username
from t t2
where t1.id = 1 and t2.id = 2;
delete from t
where t.id = 2;
Here is a db<>fiddle.
ERREUR: le nom de la table « WU_Users » est spécifié plus d'une fois CONTEXTE : instruction SQL « UPDATE "WU_Users" "WU_Users" SET "username" = "WU_Users.username" from "WU_Users" "WU_Users" WHERE "WU_Users"."id" = 1 and "WU_Users"."id" = 2 » fonction PL/pgsql inline_code_block, ligne 7 à instruction SQL
from clause.