sql - Use just inserted id in Postgres -

sql - Use just inserted id in Postgres -

i want create insert script table postgres database. table should this.

id | refid name ------------------------------------------------------------ autoinc | null | admin autoinc | null | moderator autoinc | id of moderator | readonly autoinc | id of moderator | groupadmin autoinc | id of groupadmin | rolesadmin autoinc | id of rolesadmin | users autoinc | id of users | null

my problem not know how utilize inserted id , re utilize in rest of script. illustration want insert row moderator name , utilize moderator's id in 2 inserts below.

how can accomplish that?

i have tried with

with moderator ( insert table_name (refid, name) values (null, 'moderator') returning id ) insert sd_roles (refid,rname) values ((select id moderator), 'groupadmin')

but getting nested more , need write in with.

instead of nesting, chain them:

insert table_name( refid, name ) values( id, 'admin' ); insert sd_roles( refid, name ) values( null, 'moderator' ) returning id; insert table_name( refid, name ) values( id, 'readonly' ); -- using id of moderator insert table_name( refid, name ) values( id, 'groupadmin' ) -- using id of readonly returning id; insert table_name( refid, name ) values( id, 'rolesadmin' ) -- using id of groupadmin returning id; insert table_name( refid, name ) values( id, 'users' ) -- using id of rolesadmin returning id; insert table_name( refid, name ) values( id, null ); -- using id of users

sql postgresql insert