In PSQL, is it possible to insert rows in two different tables with not-nullable foreign key columns referencing each other?

Question

I want to insert a row into two tables that reference each other using not-nullable foreign key columns.

Let's say I have table A named make

• make_id, bigint, (NOT NULL)
• name, text
• first_model_id, bigint, (NOT NULL) references model.model_id

And a table B named model

• model_id, (NOT NULL)
• name, text
• make_id, (NOT NULL) references make.make_id

Is it possible within a transaction to insert a row into both of these tables, even though each of the rows does not exist? This is easily doable by making one of the columns NULL, inserting the first row, referencing it on the second insert, then getting the new row ID from the second insert.

I'm wondering if this is possible with columns that are NOT NULL.

Answer 1

Assuming the primary key columns are defined as serial (or bigserial) you can first get the new IDs using nextval() and then use them in the subsequent inserts.

This can either be done separate statements:

select nextval(pg_get_serial_sequence('make','make_id'));
select nextval(pg_get_serial_sequence('model','model_id'));

insert into make 
  (make_id, name, first_model_id) 
values (
  currval(nextval(pg_get_serial_sequence('make','make_id'))), 
  'Make One', 
  currval(pg_get_serial_sequence('model','model_id'))
);

insert into model
  (model_id, name, make_id)
values (
  currval(pg_get_serial_sequence('model','model_id')),
  'Make One', 
  currval(nextval(pg_get_serial_sequence('make','make_id')))
);

or a single statement using a data modifying CTE

with new_ids as (
   select nextval(pg_get_serial_sequence('make','make_id')) as new_make_id, 
          nextval(pg_get_serial_sequence('model','model_id')) as new_model_id 
), new_make as (
  insert into make (make_id, name, first_model_id) 
  select new_make_id, 'Make One', new_model_id
  from new_ids
)
insert into model (model_id, name, make_id) 
  select new_model_id, 'Model 1', new_make_id
from new_ids;