记录最近用到的几种实用的PostgreSQL技巧

备份-恢复

备份
1
$ pg_dump -U username -h hostname -p port databasename -t “table1,table2” -f filename

简写

1
$ pg_dump -U postgres drupal7 > drupal.bak

官网文档

恢复
1
$ psql -U username -h hostname -d desintationdb -p port -f dumpfilename.sql

简写

1
$ psql -U postgres drupal7 < drupal.bak

官方文档

几个聚合函数

array_agg,官方有两种使用方法分别是

string_agg(*expression*,*delimiter*),参数(text, text) or (bytea, bytea),使用输入值串接成字符串,用分隔符分隔

json_agg和arrat_agg功能类似,只不过聚合成了json数组

全部聚合函数文档

去除重复的行

比如这样的一张表

id fruit
1 apple
2 apple
3 orange
4 orange
5 orange
6 Banana

删除重复的记录使用DELETE USING表达式

1
2
3
4
5
6
7
DELETE
FROM
basket a
USING basket b
WHERE
a.id < b.id
AND a.fruit = b.fruit;

官方文档中是这样描述的

PostgreSQL lets you reference columns of other tables in the WHERE condition by specifying the other tables in the USING clause. For example, to delete all films produced by a given producer, one can do:

1
2
3
> DELETE FROM films USING producers
> WHERE producer_id = producers.id AND producers.name = 'foo';
>

其中的原理应该是把filems join到producers中,然后在根据producers的条件删除。上面的例子把basket表join到自身,然后只保留id最大的那条记录,就达到了去重的目的。

在golang中使用postgresql的array类型

使用github.com/lib/pq库其中有多种array类型,几种常见的如下

  • Float64Array

  • StringArray

  • Int64Array

    ……

1
2
3
4
5
6
7
8
9
10
11
12
import (
...
"github.com/lib/pq"
...
)

...
type Block struct {
gorm.Model
.....
Tx pq.StringArray `gorm:"type:varchar(100)[]"`
}

更多类型参见官方文档