WITH
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
),
tmpcounty AS
(
   INSERT INTO
      county (county, state_id)
   VALUES
      (
         'manatee county', 5
      )
      ON CONFLICT ON CONSTRAINT county_uniq_idx DO
      UPDATE
      SET
         county = excluded.county RETURNING id, county
),
tmpcity AS
(
   INSERT INTO
      city (city)
   VALUES
      (
         'Miami'
      )
      ON CONFLICT (lower(city)) DO
      UPDATE
      SET
         city = excluded.city RETURNING id, city
)
INSERT INTO
   address (house_number, street, city_id, state_id)
   SELECT
      house_number,
      street,
      tmpcity.id,
      tmpstate.id
   FROM
      (
      VALUES
         (
            12,
            'fake st.',
            'Miami',
            'FL'
         )
      )
      val (house_number, street, city, state_code)
      LEFT JOIN
         tmpcity USING (city)
      LEFT JOIN
         tmpstate USING (state_code)
         ON CONFLICT (street) DO NOTHING