manage reference table with foreign keys on new incoming data in other table

I have a reference table (in Postgres) and multiple other tables which can reference data in/from this table. It would make sense to use foreign keys to manage this, but I was wondering how one could manage updating the entries in the reference table in the best way when new values come in.
I guess it could be done through the coding that handles the data ingestion and check and insert new values in the reference and after that add the data to the other table. This doesn’t seem ideal somehow to me, so I wondered what the best practice is in handling this situation. As there is management of deletion present in SQL I wondered if the opposite might also be available for instance? (not to prevent insertion, but sort of cascading of the values?)

Example reference table;

company_id - company_name
1 - test1
2 - test2
3 - test3

table with sales to companies:

company_id - month - year - sales
1 - January - 2020 - 10000
2 - January - 2020 - 8000
1 - December - 2019 - 9000
3 - November - 2019 - 7000

Now data can come in including rows like;

company_name - month - year - sales
test4 - January - 2020 - 10000
test5 - January - 2020 - 1000

Ideally I could insert this with one query and update the reference table with the new company name so that it gets an id that will be used in the sales table.

Answer

You can first check if the record in the reference table exists ..

SELECT 
  EXISTS(
    SELECT TRUE FROM company
    WHERE name = 'test2'); 

And in case it does you can insert the sales normally making reference to the company table. If it does not, you can use a CTE to insert the new company and get its id, so that you can outside the CTE insert the new sales record ..

WITH j AS (
  INSERT INTO company (name) 
  VALUES ('test2')
  RETURNING id
)
INSERT INTO sales (company_id, sales)
SELECT j.id, 42000 FROM j;

Testing script to add a new company

CREATE TABLE company (
id SERIAL PRIMARY KEY,
name TEXT);
INSERT INTO company (name) VALUES ('test1');

CREATE TABLE sales (
company_id INTEGER REFERENCES company(id),
sales NUMERIC);


WITH j AS (
  INSERT INTO company (name) 
  VALUES ('test2')
  RETURNING id
)
INSERT INTO sales (company_id, sales)
SELECT j.id, 42000 FROM j;


SELECT * FROM sales
JOIN company ON company.id = sales.company_id;

 company_id | sales | id | name  
------------+-------+----+-------
          2 | 42000 |  2 | test2
(1 Zeile)

If you want to ignore records that violate the foreign key constraint, check this answer by @a_horse_with_no_name.

Edit: Using anonymous code blocks including checks and inserts

DO $$
BEGIN
  IF EXISTS(SELECT TRUE FROM company WHERE name = 'test1') THEN 
      INSERT INTO sales (company_id, sales)
      VALUES ((SELECT id FROM company WHERE name = 'test1'),42000);
  ELSE
    WITH j AS (
      INSERT INTO company (name) 
      VALUES ('test1')
      RETURNING id
    )
    INSERT INTO sales (company_id, sales)
    SELECT j.id, 42000 FROM j;
  END IF;
END$$;