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"
Best Answer
There are various ways, depending on the use case. For your example:
Or:
Assuming
public.states.name
to be unique - there should be a constraint in the table definition.And a column
public.people.states_id
, obviously.