PostgreSQL的用法

### 图灵完备
PostgreSQL是图灵完备的, 也就是说这货能编程

### 帮助命令
```
\?

General
  \copyright             show PostgreSQL usage and distribution terms
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gset [PREFIX]         execute query and store results in psql variables
  \q                     quit psql

```

### version
```
select version();
                                                 version                                                  
--------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc 5.3.1 20160330, 64-bit
```

### 数据库

```sql
\l       -- 类似mysql的 show database
\c <db>  -- 类似mysql的 use <db>
\d       -- 类似mysql的 show table
\d <tbl> -- 类似mysql的 desc <tbl>
```

### 显示执行时间

```
\timing on
Timing is on.

select count(*) from txs;

  count  
---------
 1504214
Time: 3015.408 ms

----------------------------------------

select count(DISTINCT(hash)) from txs;

  count  
---------
 1504214
Time: 6081.785 ms
```

###  格式化输出

```sql
\x [on|off|auto]       toggle expanded output (currently off)

\x on  --开启
Expanded display is on.

select * from blocks where id =1;
-------------------------------------------------------------------
height      | 1
timestamp   | 1438269988
hash        | 0x88e96d4537bea4d9c05d12549907b32561d3bf31f45aae734cdc119f13406cb6
parent_hash | 0xd4e56740f876aef8c010b86a40d5f56745a118d0906a34e69aec8c0db1cb8fa3
uncle_hash  | 0x1dcc4de8dec75d7aab85b567b6ccd41ad312451b948a7413f0a142fd40d49347
coinbase    | 0x05a56e2d52c817161883f50c441c3228cfe54d9f
difficulty  | 17171480576
nonce       | 0x539bd4979fef1ec4
```

### 索引

```
create index  tbl_id_inx on tbl (id); 
```

### 修改数据表的owner
 
```
ALTER table <tbl>  OWNER TO <role>;
```

###  磁盘空间

```sql
select pg_size_pretty(pg_database_size('postgres'));

pg_size_pretty 
----------------
  237 MB

-- 表的大小
select pg_size_pretty(pg_table_size('eth_addrs'));

 table_size
------------
 520 MB
---------------------------------------------------------------------

\dt+ tbl  --表大小
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description 
--------+------+-------+----------+--------+-------------
 public | tbl  | table | postgres | 346 MB | 

---------------------------------------------------------------------

\di+ tbl_id_inx  --索引大小
                           List of relations
 Schema |    Name    | Type  |  Owner   | Table |  Size  | Description 
--------+------------+-------+----------+-------+--------+-------------
 public | tbl_id_inx | index | postgres | tbl   | 214 MB | 
```

### 临时表

```
CREATE TEMPORARY TABLE "_addrs" (
	"addr" TEXT NOT NULL,
	"balance" DECIMAL
	);	
```

### 随机数据
```sql
--新建测试表
create table tbl(id int, c1 int);  
--写入1000万随机数据
insert into tbl select generate_series(1,10000000), random()*99;  
```

### DISTINCT

```
select  count(DISTINCT(coinbase)) from blocks;
```

### 查询结果导出到文件

```
copy (select height,coinbase from blocks order by id asc limit 1000)  to '/tmp/result.txt' CSV DELIMITER ',';
```

### 循环

```sql
DO $$
BEGIN
   FOR counter IN 1..5 LOOP
 RAISE NOTICE 'Counter: %', counter;
   END LOOP;
END;

$$;
```

### 函数 存储过程

```sql
CREATE OR REPLACE FUNCTION add(a INTEGER, b NUMERIC)
RETURNS NUMERIC
AS $$
	SELECT a+b;
$$ LANGUAGE SQL;

SELECT add(95,27);
 add 
-----
 122
```

```sql
\df
                               List of functions
 Schema |       Name        | Result data type | Argument data types  |  Type  
--------+-------------------+------------------+----------------------+--------
 public | add               | numeric          | a integer, b numeric | normal

-------------------------------------------------------------------------------

DROP FUNCTION add(integer,numeric); --删除函数
```

### UNION 查询

```sql
SELECT text 'a'  UNION SELECT 'b';
```

### 数据库位置
```
show data_directory;
        data_directory        
------------------------------
 /var/lib/postgresql/9.5/main
```

---

参考:
https://github.com/digoal/blog  PostgreSQL大神德哥