postgresql 9.3 筆記-2

Reserved Words
保留字集，建table欄位取名字之前都會先掃一下，以免造成困擾～
http://www.postgresql.org/docs/9.3/static/sql-keywords-appendix.html


Operators
其中比較特別的就是::，pg轉型的寫法
http://www.postgresql.org/docs/9.3/static/sql-syntax-lexical.html#SQL-SYNTAX-OPERATORS

Check Constraints
用於輸入值的檢查，在phppgadmin中，被放在"約束"的項目底下有CHECK
Check要通過（結果要true），或是該資料值是NULL才會過喔～
reference上使用CREATE TABLE的語法說明，是可以指定名稱或沒有名稱，不過透過UI介面，理論上就會有那個名稱了

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

So, to specify a named constraint, use the key word CONSTRAINT followed by an identifier followed by the constraint definition. (If you don’t specify a constraint name in this way, the system chooses a name for you.)
A check constraint can also refer to several columns. Say you store a regular price and a discounted price, and you want to ensure that the discounted price is lower than the regular 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)
);

通過條件說明，null值也會通過，所以若要求非null且check通過的話，要額外設定非NULL的限制（Not-Null Constraints）
It should be noted that a check constraint is satisfied if the check expression evaluates to true or the null value. Since most expressions will evaluate to the null value if any operand is null, they will not prevent null values in the constrained columns. To ensure that a column does not contain null values, the not-null constraint described in the next section can be used.


Foreign Keys
外鍵，是在db裡面還蠻常用到的概念，除了基本的設定之後，外鍵的主要資料一定得先存在才能寫進去～意外發現pg裡面還有代入cascade的概念（第一次知道是從hibernate上得到的），可以自己連動處理資料還蠻方便的，但是rule得先讀懂，已免出現一些讓人傻眼的結果～

CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no),
quantity integer
);

Now it is impossible to create orders with non-NULL product_no entries that do not appear in the products table.
We say that in this situation the orders table is the referencing table and the products table is the referenced table. Similarly, there are referencing and referenced columns.

CREATE TABLE order_items (
product_no integer REFERENCES products ON DELETE RESTRICT,
order_id integer REFERENCES orders ON DELETE CASCADE,
quantity integer,
PRIMARY KEY (product_no, order_id)
);

FK的用法
http://www.postgresql.org/docs/9.3/static/sql-createtable.html
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ]

其中action可以填入

NO ACTION

Produce an error indicating that the deletion or update would create a foreign key constraint violation. If the constraint is deferred, this error will be produced at constraint check time if there still exist any referencing rows. This is the default action.

RESTRICT

Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable.

CASCADE

Delete any rows referencing the deleted row, or update the values of the referencing column(s) to the new values of the referenced columns, respectively.

SET NULL

Set the referencing column(s) to null.

SET DEFAULT

Set the referencing column(s) to their default values. (There must be a row in the referenced table matching the default values, if they are not null, or the operation will fail.)

Privileges
因為pg內的權限定義到相當的細，所以比較習慣就指定專用的owner來處裡，記得是每個table的每一樣權限都得設定，如果降子就很累了，除非是有限制只開一兩個table使用。其語法主要關鍵字用GRANT/REVOKE。

There are different kinds of privileges:
SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER,CREATE,CONNECT,TEMPORARY,EXECUTE, and USAGE.




Schemas


schema平常看起來是用不到，不過在有管理的需求上還是可以多加利用～

There are several reasons why one might want to use schemas:
•To allow many users to use one database without interfering with each other.
•To organize database objects into logical groups to make them more manageable.
•Third-party applications can be put into separate schemas so they do not collide with the names of other objects.


Partitioning
partition通常是在大量資料的查詢使用上會比較有效果，pg現在也有支援，不過看起來相當的複雜...裡頭寫了一個很大篇幅的文章，等以後真的不幸碰到再來試試吧＠＠


Foreign Data
這也是一個比較新的名詞，概念是聽來的，大概就是可以外掛一些像csv, 有format txt那樣的檔案資料， 透過下sql來做操作，因為不是真的將資料寫進DB裡，所以聽說可以應用在像log分析之類的東西上～