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