Convert a lookup value to foreign key when a query is run
There are various ways, depending on the use case. For your example:

INSERT INTO public.people(name, states_id)
VALUES ('John', (SELECT states_id from public.states WHERE name = 'CA'));


Or:

INSERT INTO public.people(name, states_id)
SELECT 'John', states_id
FROM   public.states
WHERE  name = 'CA';


Assuming public.states.name to be unique - there should be a constraint in the table definition.
And a column public.people.states_id, obviously.