update a table with serial primary key and another table with a referencing foreign key (postgres)

Question

I have two tables with a primary key/foreign key relationship

primary key table:

create table table_one (
    table_one_id_field serial,
    field1 varchar (100) not null,
    field2 varchar (100) not null,
    constraint pk__table_one primary key (table_one_id_field)
);
create unique index ix_table_one one table_one (field1, field2)

foreign key table

create table table_two (
    table_two_id int not null,
    table_one_id_field int not null, -- the foreign key
    field a int,
    field b int,
    constraint pk_table_two primary key (table_two_id)
    constraint fk_table_two foreign key (table_one_id_field) references table_one (table_one_id_field)
);

I'm not sure what the best way is to update both of these tables. My first thought was to do something like this:

insert into table_one
(field1, field2)
values
('val1', 'val2')
on conflict (field1, field2) do nothing
returning table_one_id_field

and then to use the returned value to update table_two. However, if the conflict does exist, then the id is not returned. My second idea was to run the same query without a returning statement and then to run a select query

insert into table_one
(field1, field2)
values
('val1', 'val2')
on conflict (field1, field2) do nothing;

select table_one_id_field from table_one where field1 = 'val1' and field2 = 'val2'

but I'm not sure if that is the safest way to do it. Is there another best practice way to achieve this?

Answer 1

returning can't give you the id of a row that was not inserted, so selecting again after the insert seems like the relevant approach. You can do both operations at once with common table expressions:

with 
    params as (values('val1', 'val2')) v(field1, field2),
    ins as (
        insert into table_one (field1, field2) 
        select * from params
        on conflict (field1, field2) do nothing
    )
select t.table_one_id_field 
from table_one t
inner join params p 
    on p.field1 = t.field1 and p.field2 = t.field2