Preparation:
create table t1 (id integer, v varchar);
create table t2 (id integer, v varchar);
insert into t1 values (1,''),(2,''),(3,'');
insert into t2 values (1,'A'),(3,'C'),(4,'D');
The query:
update t1 set v = t2.v from t2 where t1.id = t2.id;
The result:
select * from t1;
id | v
----+---
2 |
1 | A
3 | C
(3 rows)
tips: If you are using MySQL, the update query is different.
update t1,t2 set t1.v = t2.v where t1.id = t2.id;
copy (select * from t) to stdout with csv delimiter ',' header;
If invalid command \N
is shown when restoring a sql file, it worth trying to run with ON_ERROR_STOP
option. Another(the actual) error may be found.
psql -v ON_ERROR_STOP=1 -Uuser < foo.sql
https://stackoverflow.com/questions/20427689/psql-invalid-command-n-while-restore-sql
Here's how to reset the autoincrement field by getting the current maximum value.
select setval('table_id_seq',(select max(id) from table));