How can I modify an existing column into a foregin key in postgres? 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(*) &gt; 1;


Make sure your primary key is set on agency,

ALTER TABLE agency
  ADD PRIMARY KEY (agency_id);


Then wrap the modifications all in a transaction.

BEGIN;

    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;

COMMIT;


Output,

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,

CREATE UNIQUE INDEX
  ON agency (agency_name);
ALTER TABLE employee
  ADD FOREIGN KEY (agency)
  REFERENCES agency(agency_name);