WITH tmp AS
(
   INSERT INTO
      city (city)
   VALUES
      (
         'Miami'
      )
      ON CONFLICT (lower(city)) DO
      UPDATE
      SET
         city = excluded.city RETURNING id, city
)
, tmpstate as
(
   INSERT INTO
      state (state_code)
   VALUES
      (
         'FL'
      )
      ON CONFLICT (lower(state_code)) DO
      UPDATE
      SET
         state_code = excluded.state_code RETURNING id, state_code
)
INSERT INTO
   address (house_number, street, city_id, state_id)
   SELECT
      house_number,
      street,
      city.id,
      state.id
   FROM
      (
      VALUES
         (
            12,
            'fake st.',
            city.id,
            state.id
         )
      )
      val (house_number, street, city, state)
      LEFT JOIN
         tmp USING (city)
      LEFT JOIN
         tmpstate USING(state)
         ON CONFLICT(street) DO NOTHING