PostgreSQL筆記

yleizzz發表於2023-03-02

一.PostgreSQL簡介

PostgreSQL資料庫一種關係型資料庫。是當前世界上最先進的開源關係型資料庫。

PostgreSQL使用的是一種客戶端/伺服器的模式。一次PostgreSQL會話由以下相關程式組成：
1.postgres:一個伺服器程式（該程式管理著資料庫檔案，接收來自客戶端的連線請求，並代表客戶端對資料庫進行操作）
2.需要執行資料庫操作的客戶端應用（可能是一個字元介面的工具，或是一個圖形化的應用，或是通過訪問資料庫來顯示網頁的web伺服器等）

PostgreSQL可以處理來自客戶端的多個併發請求，這是因為它會為每個請求都fork一個新程式，而這時，客戶端和新的伺服器程式就不再通過原本的postgres進行通訊。

二.PostgreSQL基礎

1.pgAdmin4視覺化工具

pgAdmin4是專用於操作PostreSQL資料庫的視覺化工具，類似於操作MySQL的Navicat。

pgAdmin4下載地址：https://www.pgadmin.org/

具體的安裝步驟在此省略，因為網上有很多介紹安裝步驟的部落格,自行百度即可~

2.資料型別

數值型別

名字	儲存長度	描述	範圍
smallint	2位元組	小範圍整數	-32768 到 +32767
integer	4位元組	常用整數	-2147483648 到 +2147483647
bigint	8位元組	大範圍整數	9223372036854775808 到 +9223372036854775807
decimal	可變長	使用者指定精度，精確	小數點前 131072 位；小數點後 16383 位
numeric	可變長	使用者指定精度，精確	小數點前 131072 位；小數點後 16383 位
real	4位元組	可變精度，不精確	6 位十進位制數字精度
double precision	8位元組	可變精度，不精確	15 位十進位制數字精度
smallserial	2位元組	自增的小範圍整數	1 到 32767
serial	4位元組	自增整數	1 到 2147483647
bigserial	8位元組	自增的大範圍整數	1 到 9223372036854775807

貨幣型別

名字	儲存長度	描述	範圍
money	8位元組	貨幣金額	-92233720368547758.08 到 +92233720368547758.07

雖然numeric、int、bigint都可以轉換為money，但是轉換過程中會損失一部分精度，會對結果造成誤差，因此並不推薦這種做法

字元型別

名字	儲存長度	描述	範圍
character varying(n),varchar(n)	變長，有長度限制
character(n),char(n)	定長，不補足空白
text	變長，無長度限制

日期型別

名字	儲存長度	描述	最低值	最高值	解析度
timestamp [(p)] [without time zone]	8位元組	日期和時間（無時區）	4713 BC	294276 AD	1 毫秒 / 14 位
timestamp [(p)] with time zone	8位元組	日期和時間，有無時區	4713 BC	294276 AD	1 毫秒 / 14 位
date	4位元組	只用於日期	4713 BC	5874897 AD	1 天
time[(p)] [without time zone]	8位元組	只用於一天內時間	00:00:00	24:00:00	1 毫秒 / 14 位
time[(p)] without time zone	12位元組	只用於一天內時間，有時區	00:00:00+1459	24:00:00-1459	1 毫秒 / 14 位
interval [fields] [(p)]	12位元組	時間間隔	-178000000 年	178000000 年	1 毫秒 / 14 位

布林型別

名字	儲存長度	描述	範圍
boolean	1位元組	true或false

boolean型別除了true和false之外，還有第三種值：unknown，在資料庫中用NULL表示

除了以上列舉的資料型別，PostgreSQL還支援其他多種資料型別，如網路地址型別、幾何型別、文字搜尋型別等，如果想了解請自行百度~

3.建立資料庫

建立資料庫有三種方式：

使用SQL語句建立(CREATE DATABASE)
使用命令建立(createdb)
使用視覺化工具建立（pgAdmin4）

//CREATE DATABASE建立資料庫
CREATE DATABASE dbname;

//createdb命令建立資料庫
$ createdb dbname

當響應為CREATE DATABASE時，就說明成功了。
若是響應為createdb: command not found時，則說明沒有安裝好。

4.刪除資料庫

刪除資料庫有三種方式：

使用SQL語句刪除（DROP DATABASE）
使用命令刪除（dropdb）
使用視覺化工具刪除（pgAdmin4）

//使用SQL語句刪除資料庫
DROP DATABASE dbname;

//使用命令刪除資料庫
dropdb dbname

5.建立表 & 刪除表 & 修改表

//使用CREATE TABLE語法建立表
CREATE TABLE weather(
  id        int,             --主鍵ID    
  city      varchar(80),     --城市 
  temp_lo   int,             --最低氣溫       
  temp_hi   int,             --最高氣溫
  prcp      real,            --降水量
  date      date,            --日期
  PRIMARY KEY()                               
);

//使用DROP TABLE刪除表
DROP TABLE 表名

//使用ALTER修改表
ALTER TABLE table_name ADD column_name datatype;                           --新增新的列
ALTER TABLE table_name DROP column_name;                                   --刪除表中的列
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;             --修改列的資料型別
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;               --新增非空約束
ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;                 --刪除約束
ALTER TABLR table_name ADD CONSTRAINT primarykey_name PRIMARY KEY(column); --新增主鍵
ALTER TABLR table_name DROP CONSTRAINT primarykey_name;                    --刪除主鍵

real型別用於儲存單精度浮點數

在我們建立表的時候，是可以將雙劃線後面的註釋一起鍵入的，因為雙劃線後面的內容直到該行末尾都會被忽略；
SQL是對關鍵字和識別符號大小寫不敏感的語言；

6.SCHEMA模式

PostgreSQL模式可以看作是一個表的集合。
一個模式可以包含：函式、檢視、索引、操作符、據型別等。
不同模式中使用相同的物件名不會出現衝突。

模式的優點：

允許多個使用者使用一個資料庫，但不會出現衝突
將資料庫物件組織邏輯組更容易管理
第三方應用的物件可以放在獨立的模式種，可以避免與其他物件名發生衝突

//使用命令建立模式
create schema schmeaname;
create table myschema.weather(            --在myschema模式中建立一個名為weather的表
  id        int,             --主鍵ID    
  city      varchar(80),     --城市 
  temp_lo   int,             --最低氣溫       
  temp_hi   int,             --最高氣溫
  prcp      real,            --降水量
  date      date,            --日期
  PRIMARY KEY(id)                               
);


//使用SQL語句建立模式
CREATE SCHEMA schemaname;
CREATE TABLE myschema.weather(            --在myschema模式中建立一個名為weather的表
  id        int   PRIMARY KEY   NOT NULL, --主鍵ID    
  city      varchar(80),                  --城市 
  temp_lo   int,                          --最低氣溫       
  temp_hi   int,                          --最高氣溫
  prcp      real,                         --降水量
  date      date                          --日期                              
);


//刪除模式
DROP SCHEMA myschema;            --myschema模式中的物件已經被刪除乾淨
DROP SCHEMA myschema CASCADE;    --刪除myschema模式及其模式下的物件

7.INSERT INTO插入

//向表中插入新記錄
INSERT INTO weather VALUES(1,'ShangHai',46,52,0.73,'2020-7-16'),(2,'BeiJing',48,58,0.52,'2020-7-16');

上面這種寫法是標準的寫法，它要求我們記住所有欄位的順序和型別。
但是在PostgreSQL中，還有一種可以忽略欄位順序的INSERT寫法，但是這種寫法不推薦，因為很容易造成錯誤，所以這裡不再贅述，有興趣可以自己百度~

最後介紹一種使用COPY為表裝載大量資料的方法：

COPY weather FROM '/home/user/weather.txt';      --原始檔的檔名必須是後端伺服器可以訪問的，因為後端伺服器會直接讀取檔案

8.SELECT查詢

//普通查詢
SELECT * FROM weather;

//支援AS別名
SELECT w.city AS 城市, w.temp_lo AS 最低氣溫, w.temp_hi AS 最高氣溫, w.prcp AS 降水量, w.date AS 日期 FROM weather;

//支援WHERE條件查詢
SELECT * FROM weather w WHERE w.city='ShangHai' and prcp>0.0;

//支援排序
SELECT * FROM weather ORDER BY city,temp_lo;

//支援去重
SELECT DISTINCT city FROM weather ORDER BY city;

在一些資料庫（包括老版本的PostgreSQL），都支援DISTINCT預設對查詢結果排序，但是現階段不保證結果排序，所以為了保證排序正確，最好使用ORDER BY關鍵字

9.UPDATE更新

//使用UPDATE語句來更改指定的表記錄
UPDATE weather SET temp_lo=50, temp_hi=61 WHERE city='BeiJing';

10.DELETE刪除

//使用DELETE語句刪除指定表記錄
DELETE FROM weather WHERE id=1;

DELETE FROM weather;      --使用這種刪除語句的時候必須格外小心，因為這會刪除表中所有的記錄

11.運算子

運算子一般分為：

算數運算子
比較運算子
邏輯運算子
按位運算子

算數運算子

運算子	描述	例項
+	加	省略
-	減	省略
*	乘	省略
/	除	省略
%	餘	省略
^	指數	2^3 = 8
\|/	平方根	\|/25.0 = 5
\|\|/	立方根	\|\|/27.0 = 3
!	階乘	5! = 120
!!	階乘（字首操作符）	!!5 = 120

比較運算子

運算子	描述	例項
=	等於	省略
!=	不等於	省略
<>	不等於	省略
<	小於	省略
>	大於	省略
<=	小於等於	省略
>=	大於等於	省略

邏輯運算子

運算子	描述	例項
AND	且	省略
NOT	非	NOT EXISTS，NOT IN，NOT BETWEEN
OR	或	省略

按位運算子

運算子	描述	例項
&	按位與運算子	省略
\|	按位或運算子	省略
#	按位異或運算子	省略
~	取反運算子	省略
<<	二進位制左移運算子	省略
>>	二進位制右移運算子	省略

12.表示式

表示式是由一個或多個值、函式、運算子組成的。

//布林表示式：
SELECT * FROM weather WHERE id=1000;

//數字表示式
SELECT sum(temp_lo,temp_hi) FROM weather WHERE id=20;

//日期表示式
SELECT CURRENT_TIMESTAMP;

13.DISTINCT、WHERE、AND、OR、NOT NULL、LIKE、IN、NOT IN、BETWEEN、子查詢、HAVING、ORDER BY、GROUP BY、LIMIT

懶得分開寫了，反正也簡單，直接寫一起省事兒了~

//支援去重
SELECT DISTINCT city FROM weather ORDER BY city;

//支援WHERE條件查詢
SELECT * FROM weather w WHERE w.city='ShangHai';

//支援AND連線多個查詢條件
SELECT * FROM weather w WHERE w.city='ShangHai' and prcp>0.0;

//支援OR連線多個查詢條件
SELECT * FROM weather w WHERE w.city='ShangHai' OR prcp>0.0;

//支援NOT NULL判斷條件
SELECT * FROM weather w WHERE date NOT NULL;

//支援LIKE模糊查詢
SELECT * FROM weather w WHERE city LIKE'%Hai';

//支援IN條件判斷
SELECT * FROM weather w WHERE id in(1,2,3,4,5);

//支援NOT IN條件判斷
SELECT * FROM weather w WHERE id NOT IN(1,2,3,4,5);

//支援BETWEEN AND區間篩選
SELECT * FROM weather w WHERE id BETWEEN 1 AND 10;

//支援子查詢
SELECT * FROM weather w WHERE w.city exists(SELECT * FROM cities c WHERE c.id exists(1,3,7,10));

//支援HAVING結果篩選
SELECT SUM(w.temp_hi) FROM weather w WHERE w.city exists(SELECT * FROM cities c WHERE c.id exists(1,3,7,10)) HAVING w.temp_hi>60;

//支援ORDER BY排序
SELECT * FROM weather w ORDER BY w.id ASC;      --預設ASC升序，DESC降序

//支援GROUP BY分組
SELECT city,SUM(temp_hi) FROM weather GROUP BY city;

//支援LIMIT分頁
SELECT * FROM weather LIMIT 0,5;

14.WITH子句

WITH子句可以將大型、複雜二點查詢語句分解為諸多簡單的查詢語句； WITH子句中可以使用SELECT、UPDATE、INSERT、DELETE語句； WITH子句最大的優點，就是可以呼叫自身，從而實現遞迴。 WITH子句可以多次呼叫。

//建立一個名為getCities的WITH子句
WITH getCities AS(
      SELECT * FROM cities WHERE id=2;
      union all
      SELECT * FROM cities,getCities WHERE getCities .parent_id=cities.id;
)
//使用getCities
SELECT * FROM getCities ORDER BY id;

//使用WITH和RETURNING關鍵字做被刪資料備份
WITH backups AS(
      DELETE FROM weather WHERE id >100
      RETURNING *                        --RETURNING關鍵字的作用：返回DML操作的資料（增、刪、改操作）
)
INSERT INTO weather1 VALUES(SELECT * FROM backups);

三.PostgreSQL高階

1.約束

資料庫約束是用於規定表中的資料規則，用於保證資料的準確性和可靠性。
約束不可違反，違反約束會終止行為。
約束可以在建立表的時候用建表語句新增，也可以通過ALTER語句新增。
約束可以是列級或表級，列級僅適用於列，表級適用於全表。

常用的約束有：

NOT NULL 非空約束
UNIQUE 唯一約束
PARIMARY KEY 主鍵約束
FOREIGN KEY 外來鍵約束
CHECK 保證列的值都複核條件
EXCLUSION 排他約束

NOT NULL 非空約束

CREATE TABLE myschema.weather(            
  id        int   PRIMARY KEY   NOT NULL, --主鍵ID    
  city      varchar(80),                  --城市 
  temp_lo   int,                          --最低氣溫       
  temp_hi   int,                          --最高氣溫
  prcp      real,                         --降水量
  date      date                          --日期                              
);

UNIQUE 唯一約束

CREATE TABLE myschema.weather(            
  id        int   PRIMARY KEY   NOT NULL, --主鍵ID    
  city      varchar(80)         UNIQUEN,  --城市 
  temp_lo   int,                          --最低氣溫       
  temp_hi   int,                          --最高氣溫
  prcp      real,                         --降水量
  date      date                          --日期                              
);

PARIMARY KEY 主鍵約束

CREATE TABLE myschema.weather(            
  id        int   PRIMARY KEY   NOT NULL, --主鍵ID    
  city      varchar(80),                  --城市 
  temp_lo   int,                          --最低氣溫       
  temp_hi   int,                          --最高氣溫
  prcp      real,                         --降水量
  date      date                          --日期                              
);

FOREIGN KEY 外來鍵約束

CREATE TABLE myschema.weather(            
  id        int   PRIMARY KEY         NOT NULL, --主鍵ID    
  city      varchar(80) reference cities(name), --城市 
  temp_lo   int,                                --最低氣溫       
  temp_hi   int,                                --最高氣溫
  prcp      real,                               --降水量
  date      date                                --日期                              
);

CHECK 保證列的值都複核條件

CREATE TABLE myschema.weather(            
  id        int   PRIMARY KEY         NOT NULL, --主鍵ID    
  city      varchar(80) reference cities(name), --城市 
  temp_lo   int,                                --最低氣溫       
  temp_hi   int,                                --最高氣溫
  prcp      real               CHECK(real > 0), --降水量
  date      date                                --日期                              
);

EXCLUSION 排他約束

CREATE TABLE myschema.weather(            
  id        int   PRIMARY KEY         NOT NULL, --主鍵ID    
  city      varchar(80) reference cities(name), --城市 
  temp_lo   int,                                --最低氣溫       
  temp_hi   int,                                --最高氣溫
  prcp      real,                               --降水量
  date      date,                               --日期                              
  EXCLUDE USING gist  --排他約束（USING gist是用於構建和執行的一種索引型別）
  (city WITH <>,      --城市不同，日期相同，否則資料插入失敗
  date WITH =)
);

2.JOIN表連線

常用的表連線有：

INNER JOIN 內連線
LEFT JOIN 左外連線
RIGHT JOIN 右外連線
FULL JOIN全連線
CROSS JOIN交叉連線
自連線

//內連線
SELECT * FROM weather,cities WHERE weather.city=cities.name;
或
SELECT * FROM weather INNER JOIN cities ON (weather.city=cities.name);

//左外連線
SELECT * FROM weather LEFT JOIN cities ON (weather.city=cities.name);

//右外連線
SELECT * FROM weather RIGHT JOIN cities ON (weather.city=cities.name);

//自連線
SELECT * FROM weather w1,weather w2 WHERE w1.temp_lo > w2.temp_lo AND w1.temp_hi < w2.temp_hi;

//交叉連線
SELECT * FROM weahter student s CROSS JOIN result r WHERE s.student_id=r.student_id; //A表的每一行匹配B表的每一行，即A*B行

使用CROSS JOIN交叉查詢的時候需要格外注意，因為當量表資料量都較大的時候，可能會產生一個非常龐大的結果表（交叉查詢A表和B表，其結果表為A*B的資料量）

3.UNION

UNION有兩種使用方式：

UNION 不包含重複的資料
UNION ALL 包含重複的資料

//UNION
SELECT * FROM weather201906 where city='ShangHai'
UNION
SELECT * FROM weather202006 where city='ShangHai'

//UNION ALL
SELECT * FROM weather201906 where city='ShangHai'
UNION ALL
SELECT * FROM weather202006 where city='ShangHai'

4.AS別名

AS別名過於簡單，不再贅述

5.觸發器

觸發器是資料庫的回撥函式，可以在指定的資料庫事件發生時，自動執行和呼叫。
觸發器的關鍵點：

觸發器的觸發情況：

在執行操作之前(在檢查約束並進行增刪改之前)
在執行操作之後(在檢查約束並進行增刪改之後)
更新操作（對一個檢視進行增刪改時）

觸發器的FOR EACH ROW屬性是可選的，若選中，則操作修改時每行都呼叫一次，若是選中FOR EACH STATEMENT，則無論修改多少次，都只執行一次
WHEN子句和觸發器操作在引用NEW.column-name和OLD.column-name單標插入、刪除和更新的時候，可以訪問每一行元素。column-name是與觸發器關聯的表中的列名稱
若存在WHEN子句，則觸發器只會執行WHEN條件成立的那一行，若沒有WHEN子句，則每一行都會執行
BEFORE和AFTER關鍵字決定何時執行觸發器操作
要修改的表必須都存在同一個庫中，作為觸發器被附加的表或檢視，必須使用tablename，而不能是database.tablename
當建立約束觸發器時會執行約束選項，通過約束選項我們可以設定觸發器的觸發時間，當約束觸發器實現的約束被違反的時候，它會丟擲異常

//建立觸發器的語法
CTEATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
  --觸發器邏輯
];

//建立觸發器例項
CREATE TRIGGER weather_trigger AFTER INSERT ON weather FOR EACH ROW 
BEGIN
   DECLARE s1 VARCHAR(40) character set utf8;
   DECLARE s2 VARCHAR(20) character set utf8;
   SET s2 = "is created";
   SET s1 = CONCAT(NEW.name,s2);
   INSERT INTO logs(log) VALUES(s1);   
END $
DELIMITER;

//列出當前資料庫中所有的觸發器
SELECT * FROM pg_trigger;

//列出特定表的觸發器
SELECT tgname FROM pg_trigger,pg_class WHERE tgreid=pg_class.oid AND relname='company';

//刪除觸發器
DROP TRIGGER 觸發器名 ON 表名；

雖然本文介紹了觸發器，但是日常開發中並不推薦使用觸發器，原因如下： 1. 觸發器隱藏在資料庫中，數量不多還行，大數量的觸發器對於日常開發和維護，都是一個極大地挑戰，尤其是對於那些不是自己寫的觸發器而言； 2. 觸發器會佔用資料庫效能； 3. 在資料庫遷移時，會非常麻煩；簡而言之，觸發器就像是實體外來鍵一樣，非常雞肋......

6.INDEX索引

索引是一種特殊的加速資料檢索的特殊表查詢，它是一個指向表中資料的指標。索引就像是一本書的目錄。索引可以加速SELECT語句和WHERE查詢資料的速度，但是會降低UPDATE和INSERT語句的速度。索引的建立使用的是CREATE INDEX語句，需要指定表和要新增索引的列（一或多），並指定索引是升序或降序排列。索引是唯一的。

常用的索引型別：

單列索引
組合索引
唯一索引
區域性索引
隱式索引

單列索引

//語法
CREATE INDEX index_name ON table_name (column_name); --在一列上新增索引

組合索引

//語法
CREATE INDEX index_name ON table_name (column_name1,column_name2); --在多列上新增索引

單列索引和多列索引必須使用在WHERE子句過濾條件非常頻繁的列上

唯一索引

//語法
CREATE UNIQUE INDEX index_name ON table_name (column_name);  --唯一索引可以保證資料的完整性

區域性索引

//語法
CREATE INDEX index_name ON table_name (conditional_expression);  --區域性索引是在表的子集上建立的索引

隱式索引

隱式索引是在建立物件的時候，由資料庫自動建立的索引，如主鍵約束和唯一約束。

DROP INDEX刪除索引

DROP INDEX index_name;

索引不適合使用的場景： 1. 索引不應該建立在較小的表上； 2. 索引不能建立在有頻繁更新和插入的表上； 3. 索引不能建立在有大量NULL值得列上； 4. 索引不能建立在程式操作的列上

7.TRUNCATE TABLE刪除表資料

TRUNCATE TABLE會刪除表資料，但是會保留表結構。雖然DROP TABLE也能夠刪除表資料，但是會同時刪除表結構。雖然DELETE的效果相同，但是TRUNCATE TABLE更快，可以立即釋放表空間，而且不需要後續使用VACUUM操作，因為它不需要檢索資料。（VACUUM是PGSQL用於釋放、再利用被更新、刪除操作佔據的表空間）

//語法
TRUNCATE TABLE table_name;

//例項
TRUNCATE TABLE weather;

8.View 檢視

檢視並不是一張真正的表，而是通過名稱儲存在資料庫中的SQL語句。檢視是一個預定義的SQL查詢形式存在的表組合。檢視可以包括一個表所有的行（或一行或多行）。檢視可以通過一個或多個表查詢（取決於SQL查詢中有幾張表）。檢視是一個虛擬表，可以允許我們限制資料訪問或彙總各表中的資料。檢視是隻讀的，不能進行增刪改操作。

//建立檢視語法
CREATE [TEMP|TEMPORARY] VIEW view_name AS
SELECT * FROM table_name WHERE [condition];

//例項
CREATE VIEW WEATHER_VIEW AS
SELECT city,date FROM weather;

//使用檢視
SELECT * FROM WEATHER_VIEW;

//刪除檢視
DROP VIEW WEATHER_VIEW;

9.TRANSACTION 事務

事務的簡介： TRANSACTION事務是資料庫管理系統執行過程中的一個邏輯單位，由一個有限的資料庫操作序列組成。事務通常包含了讀寫操作。當事務被提交到了DBMS（資料庫管理系統），DBMS需要確保所有的操作都獨立執行、互不干擾，但同時又是一個整體，如果一個操作失敗，那麼久整體回滾。

事務的特性：

原子性：事務作為一個整體，其中的操作要麼全部執行成功，要麼一個失敗整體回滾；
一致性：確保資料庫從一個一致狀態轉換到另一個一致狀態；
隔離性：多個事務併發執行時，一個事務的執行並不會影響到其他事務的執行；
永續性：被提交的修改應永久儲存到資料庫中；

開啟事務使用BEGIN關鍵字，結束事務使用END關鍵字，提交事務操作使用COMMIT關鍵字，回滾事務使用ROLLBACK關鍵字。

BEGIN ;
      [事務操作1];
      [事務操作2];
COMMIT;

10.LOCK 鎖

LOCK鎖主要是為了保證資料庫資料的一致性，可以阻止使用者修改一行或整個表，一般用在高併發的資料庫中。

資料庫中兩種基本鎖：

排他鎖(Exclusive Locks):其他事務不可以讀取和修改；
共享鎖(Share Locks)：其他事務可以讀取，但是不能修改；

//LOCK語法
LOCK [TABLE]
name            --被鎖表名 
  IN
lock_mode;      --鎖定模式

死鎖

死鎖可能發生在兩個事務彼此等待對方結束的時候。雖然可以回滾結束它，但是不方便。最好可以指定鎖順序避免死鎖發生。

11.子查詢

有主流資料庫使用經驗的，對子查詢應該非常熟悉了，這裡不再贅述~

12.SERIAL自增長

一開始我還以為PostgreSQL沒有自帶的自增長呢，原來它的自增長是SERIAL標識欄位（和其他主流資料庫都不同，算是它的一個特色吧）

偽型別	儲存大小	範圍
SMALLSERIAL	2位元組	1 到 32,767
SERIAL	4位元組	1 到 2,147,483,647
BIGSERIAL	8位元組	1 到 922,337,2036,854,775,807

//使用SERIAL自增標識
CREATE TABLE weather(
      id       SERIAL      PRIMARY KEY,
      temp_lo  INT,
      temp_hi  INT,
      date     DATE 
)

雖然這個標識挺方便的，但是還是建議使用序列作為自增長~

13.SEQUENCE序列

PG的序列語法和Oracle的基本一致，但是細節上有區別，例如序列的快取記憶體，Oracle的可以設定為0，但是PG的最低設定為1

//建立序列
CREATE SEQUENCE complaint_opr.seq_complaint_comment_id
    INCREMENT 1
    START 500
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

14.GANT許可權

這個主流資料庫都有的，所以應該非常熟悉了

//應用在序列的時候
CREATE SEQUENCE cp_opr.seq_cp_comment_id
    INCREMENT 1
    START 500
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

ALTER SEQUENCE cp_opr.seq_cp_comment_id
    OWNER TO cp_opr;

GRANT ALL ON SEQUENCE cp_opr.seq_cp_comment_id TO cp_opr;

GRANT SELECT, UPDATE ON SEQUENCE cp_opr.seq_cp_comment_id TO tp_cp_opr;



//應用在建表的時候
--建表語句省略~
GRANT INSERT, SELECT, UPDATE ON TABLE cp_opr.t_cp_parameter TO tp_cp_opr;

GRANT ALL ON TABLE cp_opr.t_cp_parameter TO complaint_opr;

COMMENT ON TABLE cp_opr.t_cp_parameter
    IS '簡單類別定義表';

15.常用函式

都說了是常用函式了，所以只列出來常用的那部分了哈，可不是我懶~~~

函式	返回值型別	描述	例子	結果
string \| \| string	text	字串連線	'Hello' \| \| 'World'	'HelloWorld'
char_length(string)	int	返回字串中字元個數	char_length('HelloWorld')	10
convert(string using conversion_name)	text	轉換字串編碼	convert(convert('PostgreSQL' using iso_8859_1_to_utf8))	'PostgreSQL'
lower(string)	text	字串轉換成小寫	lower("TOM")	'tom'
upper(string)	text	把字串轉化為大寫。	upper('tom')	TOM
overlay(string placing string from int [for int])	text	替換子字串	overlay('Txxxxas' placing 'hom' from 2 for 4)	Thomas
substring(string [from int] [for int])	text	抽取子字串	substring('Thomas' from 2 for 3)	hom
trim([leading丨trailing 丨 both] [characters] from string)	text	從字串string的開頭/結尾/兩邊/ 刪除只包含characters(預設是一個空白)的最長的字串	trim(both 'x' from 'xTomxx')	Tom
convert(string text, [src_encoding name,] dest_encoding name)	text	字串轉碼	convert( 'text_in_utf8', 'UTF8', 'LATIN1')	以ISO 8859-1編碼表示的text_in_utf8
initcap(text)	text	單詞首字母大寫	initcap('hi thomas')	Hi Thomas
length(string text)	int	string中字元的數目	length('jose')	4
md5(string text)	text	MD5加密字串	md5('abc')
replace(string text, from text, to text)	text	替換指定字串	replace('abcdefabcdef', 'cd', 'XX')	abXXefabXXef
substr(string, from [, count])	text	抽取子字串。	substr('alphabet', 3, 2)	ph
to_char(timestamp, text)	text	將時間戳轉換為字串	to_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text)	text	將時間間隔轉換為字串	to_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text)	text	整型轉換為字串	to_char(125, '999')
to_char(double precision, text)	text	雙精度轉換為字串	to_char(125.8::real, '999D9')
to_char(numeric, text)	text	數字轉換為字串	to_char(-125.8, '999D99S')
to_date(text, text)	date	字串轉換為日期	to_date('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)	numeric	轉換字串為數字	to_number('12,454.8-', '99G999D9S')
to_timestamp(text, text)	timestamp	轉換為指定的時間格式 time zone convert string to time stamp	to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(double precision)	timestamp	把UNIX紀元轉換成時間戳	to_timestamp(1284352323)

參考文章和手冊

1.PostgreSQL菜鳥手冊
2.PostgreSQL中文手冊
3.一些其他零散的部落格~

postgresql 筆記
2018-04-26
SQL筆記
postgreSql 使用筆記
2018-10-24
SQL筆記
Postgresql學習筆記
2017-07-17
SQL筆記
【PostgreSQL】入門學習筆記
2022-04-19
SQL筆記
筆記：Node.js Postgresql踩坑
2019-02-11
筆記Node.jsSQL
【讀書筆記】Postgresql清理過程
2021-03-03
筆記SQL
筆記：PostgreSQL 、Node.js 、函式計算
2019-02-11
筆記SQLNode.js函式
筆記:如何使用postgresql做順序扣減庫存
2021-09-01
筆記SQL
【讀書筆記】Postgresql連線方法及執行器
2021-03-06
筆記SQL
PostgreSQL小記
2016-12-28
SQL
讀書筆記】《PostgreSQL指南-內幕探索》-2.程式和記憶體架構
2021-01-11
筆記SQL記憶體架構
《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之一：外連線
2016-07-26
筆記SQLOracle
PostgreSQL：記憶體結構
2023-02-17
SQL記憶體
【讀書筆記】《PostgreSQL指南-內幕探索》-8.緩衝區管理器
2021-02-24
筆記SQL
《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之四：特殊字元和符號
2016-08-04
筆記SQLOracle字元符號
印象筆記 --- 方法分享筆記
2018-11-22
筆記
讀書筆記】《PostgreSQL指南-內幕探索》-3.2單表查詢的代價估計
2021-01-16
筆記SQL
【讀書筆記】《PostgreSQL指南-內幕探索》-3.3建立單表查詢的計劃樹
2021-01-18
筆記SQL
《卸甲筆記》-PostgreSQL和Oracle的資料型別的對比系列五：其它型別
2016-07-15
筆記SQLOracle資料型別
《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之五：函式的差異(五)
2016-08-18
筆記SQLOracle函式
《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之五：函式的差異(三)
2016-08-12
筆記SQLOracle函式
《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之五：函式的差異(一)
2016-08-08
筆記SQLOracle函式
筆記
2020-12-28
筆記
【讀書筆記】《PostgreSQL指南-內幕探索》-7.堆內元組和僅索引掃描
2021-02-24
筆記SQL索引
【讀書筆記】《PostgreSQL指南-內幕探索》-3.1-解析器分析器等介紹
2021-01-14
筆記SQL
CUUG筆記 ORACLE索引學習筆記
2014-01-07
筆記Oracle索引
主動筆記與被動筆記
2015-04-12
筆記
淘寶記錄筆記
2024-04-18
筆記
《卸甲筆記》-PostgreSQL和Oracle的資料型別的對比系列四：大資料型別
2016-07-13
筆記SQLOracle資料型別大資料
PostgreSQL,SQLServer邏輯增量(通過邏輯標記update,delete)同步到Greenplum,PostgreSQL
2018-06-21
SQLServerdelete
心情筆記
2020-04-04
筆記
命令筆記
2019-12-02
筆記
筆記：Docker
2019-02-26
筆記Docker
Meteor筆記
2019-03-01
筆記
ES筆記
2019-04-03
筆記
AbstractQueuedSynchronizer筆記
2019-04-02
筆記
Redis筆記
2019-03-24
Redis筆記
new筆記
2019-07-14
筆記