title: Postgresql - change foreign key to another column
body: <p>I have two tables:</p>
  
  <pre><code>create table test1 (
  oldPrimaryKey varchar(100) primary key, 
  someText varchar(100)
  )
  
  create table test2 (
  someText varchar(100),
   oldPrimaryKey varchar(100) references test1(oldPrimaryKey)
  )
  </code></pre>
  
  <p>I need to change <code>oldPrimaryKey</code> to new one so I do this:</p>
  
  <ol>
  <li><p>Drop old one key:</p>
  
  <ul>
  <li>alter table test1 drop constraint test1_pkey;</li>
  <li>alter table test2 drop constraint test2_oldprimarykey_fkey;</li>
  </ul></li>
  <li><p>Create new column:</p>
  
  <ul>
  <li>alter table test1 add column newPrimaryKey bigint;</li>
  </ul></li>
  <li>Populate it using sequence:
  <ul>
  <li>create sequence add_id_seq start with 1 increment by 1 cache 1;</li>
  <li>update test1 set newPrimaryKey = nextval('add_id_seq');</li>
  </ul></li>
  <li>Change “newPrimaryKey” column to be primary key:
  <ul>
  <li>alter table test1 add constraint keyName primary key(newPrimaryKey);</li>
  </ul></li>
  </ol>
  
  <p>Now I have something like this:</p>
  
  <pre><code>CREATE TABLE test1
  (
    oldprimarykey character varying(100) NOT NULL,
    sometext character varying(100),
    newprimarykey bigint NOT NULL,
    CONSTRAINT keyname PRIMARY KEY (newprimarykey)
  )
  
  CREATE TABLE test2
  (
    sometext character varying(100),
    oldprimarykey character varying(100),
    CONSTRAINT test2_oldprimarykey_fkey FOREIGN KEY (oldprimarykey)
        REFERENCES test1 (oldprimarykey) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION
  )
  </code></pre>
  
  <p>I don’t know what to do now (some script?). I need to change value from table <code>test2</code> column <code>oldprimarykey</code> to corresponding value from table <code>test1</code> column <code>newprimarykey</code>.</p>
  
  <p>If it is too chaotic I will try to give an example of the normal data:</p>
  
  <p>Rows from table1 (oldprimarykey, sometext, newprimarykey):</p>
  
  <ol>
  <li>test@test.com, ‘some text’, 1</li>
  <li>test2@test.com, ‘more text’, 2</li>
  </ol>
  
  <p>Corresponding  rows from table2 (sometext, oldprimarykey):</p>
  
  <ol>
  <li>‘some text’, test@test.com</li>
  <li>‘text text’, test2test.com</li>
  </ol>
  
  <p>I want to change email adress from table2 to corresponding  int from table1.</p>