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;

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));