FOR UPDATE clause with outer join from ORACLE TO PostgreSQL-CodePudding

Can somebody tell me how I can convert the following Oracle SQL Statement in PostgreSQL? Postgres cant lock table in outer join.

SELECT
    TB1.ID AS USER_ID,
    TB1.USER_NAME AS USER_NAME,
    TB1.BIRTHDATE AS BIRTHDATE ,
    TB2.AGE AS AGE,
    TB3.GENDER AS GENDER ,
    TB4.SUBJECT AS SUBJECT ,
FROM
    TABLE1  AS TB1
LEFT JOIN TABLE2 AS TB2 ON
    TB1.ID= TB2.ID
LEFT JOIN TABLE3 AS  TB3 ON
    TB1.ID= TB3.ID
LEFT JOIN TABLE4  AS TB4 ON
    TB1.ID= TB4.ID 
FOR UPDATE OF TB2,TB3,TB4 NOWAIT ;

CodePudding user response：

Create a Common table expression (CTE) where each auxiliary statement is of the form SELECT... table1 JOIN <table[2|3|4]> ...FOR UPDATE OF <table[2|3|4]>.

Then, in the primary statement, LEFT JOIN all of the auxiliary statements together with table1 to compute the final result set.

This looks like:

WITH ages as (
SELECT
  tb1.id,
  tb2.age
FROM
  table1 as tb1
  JOIN tb2 as table2 ON tb1.id = tb2.id
  FOR UPDATE OF tb2 NOWAIT

),

genders as (
SELECT
  tb1.id,
  tb3.gender
FROM
  table1 as tb1
  JOIN table3 tb3 ON tb1.id = tb3.id
  FOR UPDATE OF tb3 NOWAIT

),

subjects as (
SELECT
  tb1.id,
  tb4.subject
FROM
  table1 tb1
  JOIN table4 tb4 ON tb1.id = tb4.id
  FOR UPDATE OF tb4 NOWAIT
)

SELECT
  tb1.id user_id,
  tb1.user_name,
  tb1.birthdate,
  ages.age,
  genders.gender,
  subjects.subject

FROM
  table1 tb1
  LEFT JOIN ages ON ages.id = tb1.id
  LEFT JOIN genders ON genders.id = tb1.id
  LEFT JOIN subjects ON subjects.id = tb1.id