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:
INSERT INTO public.people(name, state) VALUES ('John', 'CA');
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);
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"