2

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"

  • I presume id is a number? Why do this substitution at all? I thought everyone in the States understood the two letter codes (and indeed in a good part of the world also)? – Vérace Feb 22 '16 at 21:42
  • @Vérace Like I told MDCCL the state object is just an example. In production we may use lookup tables for product categories, stock locations, etc. – Caleb Feb 23 '16 at 20:15
  • @Erwin I added the information you requested to the original post. – Caleb Feb 23 '16 at 20:16
1

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.

  • Thanks for your response. I agree these are the two best ways to do this if you can run multiple queries in the same command. The interface we are using to talk with Postgres limits us to a single query which puts some limitations on the way we can do this, but it is still possible by running a seperate SELECT query to get the correct ID. What I was really wondering is if there was a column property or special constraint that could map something like a Text value to an ID automatically. – Caleb Feb 23 '16 at 20:00
  • 1
    @Caleb: Erwin's answer shows two alternative solutions using a single query. – a_horse_with_no_name Feb 23 '16 at 20:05
  • @a_horse_with_no_name The interface I was referring to constructs the query for you, sorry for the confusion. Like I said in my previous comment, the alternative would be to make a separate SELECT query to pull the ID and then run the Insert query with it. I am posting this to StackOverflow in an attempt to find a solution at the schema/database level. Possibly a schema definition or database trigger. If there isn't another way I will have to modify the legacy system to identify which field is supposed to be a foreign key and then do a SELECT query to determine the correct ID. – Caleb Feb 23 '16 at 20:20
  • 1
    @Caleb: Like a_horse said, these are single queries. If your interface does not allow such basic queries, I would consider changing the interface. – Erwin Brandstetter Feb 23 '16 at 21:15
  • 1
    Why can't a legitmate single query request not supported? Is there not another way? – Vérace Feb 23 '16 at 23:42
0

Why can't a legitmate single query request not supported? Is there not another way?

Erwin shows really well a single query that works. But, if you think this is harder than need be it's because the data relates to each other not on the abbreviation but on the surrogate key. The surrogate key is nonsense you created. Because you created this nonsense, you have to handle it. There are other ways you can do this. Such as using a natural key with or without an ENUM type

From my perspective having a surrogate key on a table with less than 100 rows is relatively pointless when you have natural key like a state abbreviation. In some cases this is more arguable, but in this case there is even a standard for it. Shy of Puerto Rico becoming a state (highly unlikely), or Texas withdrawing from the Union after the communist revolution deposes the fascists, this is unlikely to change in your lifetime.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.