Copying a column of data from one table to another in PostgreSQL

Question

I have the following setup:

Table1:

latdouble
3
4
5
1
6
2
6
9

Table2:

time    latdouble(type double)    latvarchar(type varchar)   
2:00                              3
3:00                              4
4:00                              5
5:00                              1
6:00                              6
7:00                              2
8:00                              6
9:00                              9

Basically latdouble in Table1 is in the correct order and I would like to copy the values into Table2 as they are, the result should be:

Table2:

time    latdouble(type double)    latvarchar(type varchar)   
2:00    3                         3
3:00    4                         4
4:00    5                         5
5:00    1                         1
6:00    6                         6
7:00    2                         2
8:00    6                         6
9:00    9                         9

As far as I'm aware I need to use a command similar to:

update Table2 set latdouble = (select latdouble from Table1)

However I get the following error:

ERROR: more than one row returned by a subquery used as an expression

I'm sure there's a simple fix but I can't figure it out,

Thanks, James

UPDATE: Let me just explain how this came about. Table 2 originally looked like:

Table2:

time    latvarchar(type varchar)   
2:00    3                         
3:00    4                         
4:00    5                         
5:00    1                         
6:00    6                         
7:00    2                         
8:00    6                         
9:00    9

I added the latdouble(type double) column to Table2.

I then created a new table called Table1 which contains a column called latdouble(type double). I used the following command to cast latvarchar from Table2 and copy it into Table1:

insert into Table1 (latdouble) select cast(latvarchar as double precision) from Table2

And now I want to copy the values back from Table1 latdouble to Table2 latdouble. The reason I did this is to cast the values from latvarchar to double and save them in latdouble without having to create a temporary copy of the whole of Table2 as it is a very large table with a few indexes.

Answer 1

If I'm understanding correctly, there's no need to use Table1 at all. You can simply set the latdouble value for each row in place using UPDATE:

UPDATE Table2 SET latdouble = cast(latvarchar as double precision);

UPDATE

To do this incrementally, I can think of 2 options.

Option 1. (Quick and Dirty)

UPDATE Table2 
SET latdouble = cast(latvarchar as double precision)
WHERE tKey IN (SELECT tKey FROM Table2 WHERE latdouble IS NULL LIMIT 10000);

Run this many times until no rows are updated anymore (which means no latdouble fields are NULL)

Option 2. (Use a cursor and commit incrementally)

Following the method in this article, you can run this python script:

#!/home/postgres/python/bin/python
#
# incremental commits
# 2008 kcg

import psycopg2
import time

# vars
incremental_commit_size=10000   # number of rows
throttle_time=0         # seconds

connectstr="host=localhost dbname=postgres user=postgres port=5432"
handle=psycopg2.connect(connectstr)
cursor=handle.cursor()
cursor2=handle.cursor()
sql="select tKey from table2"
cursor.execute(sql)

while 1:

 output = cursor.fetchmany(incremental_commit_size)

 if not output:
  break
 for row in output:

   # update table
   sql="update table2 set latdouble = cast(latvarchar as double precision) where tKey = %s"
   cursor2.execute(sql,([row[0]]))

 #commit, invoked every incremental commit size
 handle.commit()
 time.sleep(throttle_time)

handle.commit()