postgres學習筆記

將一個識別符號變得受限同時也使它變成大小寫敏感的，反之非受限名稱總是被轉換成小寫形 式，例如，識別符號FOO、foo和"foo"在PostgreSQL中被認為是相同的，而"Foo"和"FOO"則互 不相同且也不同于前面三個識別符號（PostgreSQL將非受限名字轉換為小寫形式與SQL標準是不兼容 的，SQL標準中要求將非受限名稱轉換為大寫形式，這樣根據標準， foo應該和 "FOO"而不是"foo"相同，如果希望寫一個可移植的應用，我們應該總是用引號修飾一個特定名字或者從不使用引號修飾），

查詢自動中添加單引號的方法

SQL> select '123''1234'
"123'1234"

查詢轉義字符需要在欄位前加入E

SQL> select E'123\rfoo\n1234'
"123
foo
1234"

使用美元來代替單引號

SQL> select $$Dianne's horse$$
"Dianne's horse"

位置引數$number

CREATE FUNCTION dept(text) RETURNS test.dept
    AS $$ SELECT * FROM test.dept WHERE name = $1 $$
    LANGUAGE SQL;
這里$1參考函式被呼叫時第一個函式引數的值，

聚集運算式

aggregate_name (expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name (ALL expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name ( [ expression [ , ... ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHERE filter_clause ) ]

大部分聚集函式忽略空輸入，這樣其中一個或多個運算式得到空值的行將被丟棄，除非另有說明，對于所有內建聚集都是這樣，

例如，count(*)得到輸入行的總數，count(f1)得到輸入行中f1為非空的數量，因為count忽略空值，而count(distinct f1)得到f1的非空可區分值的數量，

在處理多引數聚集函式時，注意ORDER BY出現在所有聚集引數之后，

SELECT string_agg(a, ',' ORDER BY a) FROM table;

如果指定了FILTER，那么只有對filter_clause計算為真的輸入行會被交給該聚集函式，其他行會被丟棄，例如：

SELECT
    count(*) AS unfiltered,
    count(*) FILTER (WHERE i < 5) AS filtered
FROM generate_series(1,10) AS s(i);
 unfiltered | filtered
------------+----------
         10 |        4
(1 row)

視窗函式

function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )

其中window_definition的語法是

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

而可選的frame_clause是下列之一

{ RANGE | ROWS } frame_start
{ RANGE | ROWS } BETWEEN frame_start AND frame_end

其中frame_start和frame_end可以是下面形式中的一種

UNBOUNDED PRECEDING
value PRECEDING
CURRENT ROW
value FOLLOWING
UNBOUNDED FOLLOWING

型別轉換

CAST ( expression AS type )
expression::type

CAST語法遵從 SQL，而用::的語法是PostgreSQL的歷史用法，

創建function樣例

CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false)
RETURNS text
AS
$$
 SELECT CASE
        WHEN $3 THEN UPPER($1 || ' ' || $2)
        ELSE LOWER($1 || ' ' || $2)
        END;
$$
LANGUAGE SQL IMMUTABLE STRICT;

SELECT concat_lower_or_upper('Hello', 'World', true);
 concat_lower_or_upper 
-----------------------
 HELLO WORLD
(1 row)

SELECT concat_lower_or_upper('Hello', 'World');
 concat_lower_or_upper 
-----------------------
 hello world
(1 row)

使用命名記號

SELECT concat_lower_or_upper(a => 'Hello', b => 'World', uppercase => true);
 concat_lower_or_upper 
-----------------------
 HELLO WORLD
(1 row)

SELECT concat_lower_or_upper(a => 'Hello', uppercase => true, b => 'World');
 concat_lower_or_upper 
-----------------------
 HELLO WORLD
(1 row)

SELECT concat_lower_or_upper('Hello', 'World', uppercase => true);
 concat_lower_or_upper 
-----------------------
 HELLO WORLD
(1 row)

為了向后兼容性，基于 ":=" 的舊語法仍被支持：

SELECT concat_lower_or_upper(a := 'Hello', uppercase := true, b := 'World');
 concat_lower_or_upper 
-----------------------
 HELLO WORLD
(1 row)

常見資料型別：

用于整數的integer；可以用于分數的numeric；用于字串的text，用于日期的date，用于一天內時間的time以及可以同時包含日期和時間的timestamp，

一個表能夠擁有的列的資料是有限的，根據列的型別，這個限制介于250和1600之間，

插入時默認主鍵值

CREATE TABLE products (
    product_no integer DEFAULT nextval('products_product_no_seq'),
    ...
);
速寫
CREATE TABLE products (
    product_no SERIAL,
    ...
);

約束：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);
給約束起名
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);
多列約束
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0 AND price > discounted_price)
);

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CONSTRAINT valid_discount CHECK (price > discounted_price)
);

非空約束
CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

一列多種約束
CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);
空約束，不存在約束，只是腳本中方便切換非空約束才這么寫，沒實際意義
CREATE TABLE products (
    product_no integer NULL,
    name text NULL,
    price numeric NULL
);

唯一約束
CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE (product_no)
);
多列組成唯一約束
CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

CREATE TABLE products (
    product_no integer CONSTRAINT must_be_different UNIQUE,
    name text,
    price numeric
);

主鍵
CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);
多列主鍵
CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);
外鍵約束和排他約束一般不用

 系統隱式定義列

OID是32位量，它從一個服務于整個集簇的計數器分配而來，在一個大型的或者歷時長久的資料庫中，該計數器有可能會出現繞回，因此，不要總是假設OID是唯一的，除非你采取了措施來保證，如果需要在一個表中標識行，推薦使用一個序列生成器，然而，OID也可以被使用，但是是要采取一些額外的預防措施：

• 如果要將OID用來標識行，應該在OID列上創建一個唯一約束，當這樣一個唯一約束（或唯一索引）存在時，系統會注意不生成匹配現有行的OID（當然，這只有在表的航數目少于2³²（40億）時才成立，并且在實踐中表的尺寸最好遠比這個值小，否則將會犧牲性能），

• 絕不要認為OID在表之間也是唯一的，使用tableoid和行OID的組合來作為資料庫范圍內的識別符號，

• 當然，問題中的表都必須是用WITH OIDS創建，在PostgreSQL 8.1中，WITHOUT OIDS是默認形式，

事務識別符號也是32位量，在一個歷時長久的資料庫中事務ID同樣會繞回，但如果采取適當的維護程序，這不會是一個致命的問題，但是，長期（超過10億個事務）依賴事務ID的唯一性是不明智的，

命令識別符號也是32位量，這對一個事務中包含的SQL命令設定了一個硬極限： 2³²（40億），在實踐中，該限制并不是問題 — 注意該限制只是針對SQL命令的數目而不是被處理的行數，同樣，只有真正 修改了資料庫內容的命令才會消耗一個命令識別符號，

列操作命令

增加一列
ALTER TABLE products ADD COLUMN description text;
ALTER TABLE products ADD COLUMN description text CHECK (description <> '');
提示: 增加一個帶默認值的列需要更新表中的每一行（來存盤新列值），然而，如果不指定默認值，PostgreSQL可以避免物理更新，因此如果我們準備向列中填充的值大多是非默認值，最好是增加列的時候不指定默認值，增加列后用UPDATE填充正確的資料并且增加所需要的默認值約束，

移除列
ALTER TABLE products DROP COLUMN description;
移除時忽略外鍵的約束
ALTER TABLE products DROP COLUMN description CASCADE;

增加約束
ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

移除約束，"psql \d 表名"來查看系統默認約束名
ALTER TABLE products DROP CONSTRAINT some_name;
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

設定\移除默認值
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

修改列資料型別
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
如果需要一種更復雜的轉換，應該加上一個USING子句來指定應該如何把舊值轉換為新值，

重命名列
ALTER TABLE products RENAME COLUMN product_no TO product_number;

重命名表
ALTER TABLE products RENAME TO items;

 權限：

SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、EXECUTE以及USAGE，

joe是一個已有用戶，accounts是一個已有表，用ALL取代特定權限會把與物件型別相關的所有權限全部授權，可以在授予權限時使用"with grant option"來允許接收人將權限轉授給其他人，
GRANT UPDATE ON accounts TO joe;

撤銷權限：
REVOKE ALL ON accounts FROM PUBLIC;

 模式，將模式寫入路徑中，那么SQL查詢可以省略模式名

CREATE SCHEMA myschema;
模式.表
在模式中創建表
CREATE TABLE myschema.mytable (
 ...
);

洗掉模式
DROP SCHEMA myschema;
DROP SCHEMA myschema CASCADE;

創建一個由其他人所擁有的模式
CREATE SCHEMA schemaname AUTHORIZATION username;
以pg_開頭的模式名被保留用于系統目的，所以不能被用戶所創建，

不指定模式，默認放在public模式中，查看默認模式
SHOW search_path;
將模式放到搜索路徑中可以不用加表明進行查詢
SET search_path TO myschema,public;
SET search_path TO myschema;
公共模式沒有什么特別之處，它只是默認存在而已，它也可以被洗掉，

注意在默認情況下，所有人都擁有在public模式上的CREATE和USAGE權限，這使得用戶能夠連接到一個給定資料庫并在它的public模式中創建物件，如果不希望允許這樣，可以撤銷該權限：

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
第一個"public"是模式，第二個"public"指的是 "每一個用戶"，第一種是一個識別符號，第二種是一個關鍵詞，所以兩者的大小寫不同，

標籤：PostgreSQL

上一篇：postgres安裝

下一篇：改變Maven專案中的Java版本