CODEWITHSUNDEEP
sqlpostgresql
Md. Parvez Alam
Md. Parvez Alam

Reputation: 4596

pgsql return from source after insert

I have query like below

DROP TABLE IF EXISTS temp_details;
create temp table tmp_zone_section as 
    select  
            x.id,
            x.name,
            x.address,
                              x.identification
     FROM json_to_recordset
        (arg_json)
            as x(
            
            id integer,
            name character varying,
           address character varying,
           identification character varying
         );

with records as ( insert into details select id, name from temp_details on conflict do update set address = excluded.address returning * ) The above query returns every affected rows, I want to return an extra column from the source which was not part of either insert or update along with * like below

 DROP TABLE IF EXISTS temp_details;
    create temp table tmp_zone_section as 
        select  
                x.id,
                x.name,
                x.address,
                                  x.identification
         FROM json_to_recordset
            (arg_json)
                as x(
                
                id integer,
                name character varying,
               address character varying,
               identification character varying
             );

with records as (
insert into details
select id, name from temp_details
on conflict do update
set address = exluded.address
returning *, temp_details. identification
)

Any Idea how can it be achieved.

Thanks,

Upvotes: 0

Views: 55

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19724

If I am following correctly:

with records as (
insert into details
select id, name from temp_details
on conflict do update
set address = exluded.address
returning *
)
SELECT 
  records.*, temp_details.identification
FROM
  records
JOIN 
   temp_details
ON
   records.id = temp_details.id

Upvotes: 0

Related Questions