Convert a lookup value to foreign key when a query is run Currently we have a legacy system running queries on a Postgres server to update/insert records. One of the obstacles we are having is that we have to convert text values to the correct foreign key for a lookup table. Please see below for a better explanation:

Query:

INSERT INTO public.people(name, state)
VALUES ('John', 'CA');


Tables:

public.people

CREATE TABLE people
(
  name text,
  state smallint,
  CONSTRAINT state_key FOREIGN KEY (state)
      REFERENCES states (states_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE INDEX fki_state_key
  ON people
  USING btree
  (state);


public.states

CREATE TABLE states
(
  states_id smallint,
  name text,
  CONSTRAINT state_unique UNIQUE (states_id)
)
WITH (
  OIDS=FALSE
);


What is the best way of converting 'CA' to the associated ID in the public.states table? The particular interface we are using to talk to Postgres is called Postgrest (https://github.com/begriffs/postgrest) and would require additional SELECT statements instead of a subqueries. We want minimize the amount of calls as much as possible.

Also, on the legacy system that is using that interface it does not use a lookup table, which makes it difficult to determine which fields need to be converted for a lookup table.

Is there a native way in Postgres to handle these types of conversions? If you need additional information please let me know.

"PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit"
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.