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;
By plain(?) SQL:
Make compressed backup file.
pg_dump mydb | gzip > backup.sql.gz
The target database has to be empty.
alter database mydb rename to mydb_old;
create database mydb;
Restore the data through psql command.
gunzip backup.sql.gz
psql mydb < backup.sql
By custom format(?):
TODO: write how to create .dump file and pg_restore.
actually, which way is better?
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));