Given for example the following:

CREATE TABLE agency(agency_id, agency_name)
  AS VALUES (1,'Test Agency');

CREATE TABLE employee(employee_id,agency,first_name)
  AS VALUES (16,'Test Agency','Paul');

Desired output:

|      employee_id    |      agency_id   |     first_name   |
|          15         |         1        |       Paul       |

I populated the Employee table from a JSON using ogr2ogr and naturally it wouldn't catch that the agency value is supposed to be referenced from the Agency table.

How can I ALTER or UPDATE the existing column agency in Employee table to take the agency_id of the Agency table and change its type as foreign key?


First make sure you have no duplicate agency_names, or you may have problems.

SELECT agency_name
FROM agency
GROUP BY agency_name
HAVING count(*) > 1;

Make sure your primary key is set on agency,

  ADD PRIMARY KEY (agency_id);

Then wrap the modifications all in a transaction.


    ALTER TABLE employee
      ADD COLUMN agency_id int
      REFERENCES agency;
    UPDATE employee
      SET agency_id = a.agency_id
      FROM agency AS a
      WHERE employee.agency = a.agency_name;
    ALTER TABLE employee
      DROP COLUMN agency;



TABLE employee ;
 employee_id | first_name | agency_id 
          16 | Paul       |         1
(1 row)

Note I assumed you wanted the primary key on the agency_id based on the wording of the question. You can, as @Craig Ringer points out, create an FKEY using the text of the column too. That column will still have to be unique -- see the above. Doing so would look like this,

  ON agency (agency_name);
ALTER TABLE employee
  ADD FOREIGN KEY (agency)
  REFERENCES agency(agency_name);
  • Note that you can, if you wish, instead declare a FOREIGN KEY check on the existing column employee.agency. Nothing requires you to use a surrogate key, though it's often more efficient to do so. – Craig Ringer Oct 11 '17 at 2:56
  • @CraigRinger updated to show that too. (though that's not how I read the question) – Evan Carroll Oct 11 '17 at 3:04
  • @EvanCarroll I'll test this out! thank you for this detailed answer and options fom Craig:) – Reiion Oct 11 '17 at 3:12

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.