Output Inserted.id equivalent in Postgres

user_7991333 0
Viewed 2441

I am new to PostgreSQL and trying to convert mssql scripts to Postgres.

For Merge statement, we can use insert on conflict update or do nothing but am using the below statement, not sure whether it is the correct way.

MSSQL code:

Declare @tab2(New_Id int not null, Old_Id int not null)

MERGE Tab1 as Target
USING (select * from Tab1
        WHERE ColumnId = @ID) as Source on 0 = 1
        when not matched by Target then
    INSERT 
       (ColumnId
       ,Col1
       ,Col2
       ,Col3
      )
 VALUES (Source.ColumnId
       ,Source.Col1
       ,Source.Col2
       ,Source.Col3
       )
OUTPUT INSERTED.Id, Source.Id into @tab2(New_Id, Old_Id);

Postgres Code:

Create temp table tab2(New_Id int not null, Old_Id int not null)

With source as( select * from Tab1
        WHERE ColumnId = ID)
Insert into Tab1(ColumnId
       ,Col1
       ,Col2
       ,Col3
       )
select Source.ColumnId
        ,Source.Col1
       ,Source.Col2
       ,Source.Col3
       from source

My query is how to convert OUTPUT INSERTED.Id in postgres.I need this id to insert records in another table (lets say as child tables based on Inserted values in Tab1)

postgresql
1 Answers

In PostgreSQL's INSERT statements you can choose what the query should return. From the docs on INSERT:

The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted (or updated, if an ON CONFLICT DO UPDATE clause was used). This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any expression using the table's columns is allowed. The syntax of the RETURNING list is identical to that of the output list of SELECT. Only rows that were successfully inserted or updated will be returned.

Example (shortened form of your query):

WITH [...] INSERT INTO Tab1 ([...]) SELECT [...] FROM [...] RETURNING Tab1.id
jast_124710
Related