PostgreSQL

SQL

Update table using another table

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;

export to csv

copy (select * from t) to stdout with csv delimiter ',' header;

backup and restore

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?

Trouble shoot

error on restoration

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

fix autoincrement value

Here's how to reset the autoincrement field by getting the current maximum value.

select setval('table_id_seq',(select max(id) from table));