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(*) > 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);

StackExchange Database Administrators Q#188162, answer score: 4