subquery must return only one column during insert data for returning

is that any way for returning join column during INSERT INTO ? here is my query

insert into contacts(address,building_type, building_number, user_id, province_id, city_id, district_id, village_id) VALUES ('address one', 'apartement', 12, 1, 1 , 1, 1, 1) RETURNING address, building_type, building_number,     (select p.name as province from provinces as p where p.id = contacts.province_id),     (select c.name  as city from cities as c where c.id = contacts.city_id),     (select d.name as district from districts as d where d.id = contacts.district_id),     (select v.name as village, v.postal from villages as v where v.id = contacts.village_id); 

the last sub query is not work because i want return the villages table with two columns is that any way to pass that 2 columns ??

Add Comment
1 Answer(s)

You can use a CTE with returning . . . and then a query:

with i as (       insert into contacts(address,building_type, building_number, user_id, province_id, city_id, district_id, village_id)       values ('address one', 'apartement', 12, 1, 1 , 1, 1, 1)       returning address, rt, rw, building_type, building_number, province_id, city_id, district_id, village_id) select i.*,        (select p.name as province from provinces as p where p.id = i.province_id),        (select c.name  as city from cities as c where c.id = i.city_id),        (select d.name as district from districts as d where d.id = i.district_id),        (select v.name as village, v.postal from villages as v where v.id = i.village_id)     from i; 

However, the above is returning multiple rows for villages. Probably the best way to fix that is with proper joins:

with i as (       insert into contacts(address,building_type, building_number, user_id, province_id, city_id, district_id, village_id)       values ('address one', 'apartement', 12, 1, 1 , 1, 1, 1)       returning address, rt, rw, building_type, building_number, province_id, city_id, district_id, village_id) select i.*, p.name as province, c.name as city, d.name as district,        v.name as village, v.postal    from i left join      province p      on p.id = i.province_id left join      cities c       on c.id = i.city_id left join      districts d       on d.id = i.district_id left join      villages v       on v.id = i.village_id 
Answered on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.