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

数据库

\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

格式化输出

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

磁盘空间

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

随机数据

--新建测试表
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 ',';

循环

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

$$;

函数 存储过程

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

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

\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 查询

SELECT text 'a'  UNION SELECT 'b';

数据库位置

show data_directory;
        data_directory        
------------------------------
 /var/lib/postgresql/9.5/main

---

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