[ 文章收集 ] PostgreSQL 學習手冊 (模式 Schema)

Source From Here 
Preface 
一個數據庫包含一個或多個命名的模式，模式又包含表。模式還包含其它命名的物件，包括資料型別、函式，以及操作符。同一個物件名可以在不同的模式裡使用而不會導致衝突； 比如，schema1 和 myschema 都可以包含叫做 mytable 的表。和資料庫不同，模式不是嚴格分離的：一個使用者可以訪問他所連線的資料庫中的任意模式中的物件，只要他有許可權。 

我們需要模式有以下幾個主要原因： 

1). 允許多個使用者使用一個數據庫而不會干擾其它使用者。 
2). 把資料庫物件組織成邏輯組，讓它們更便於管理。 
3). 第三方的應用可以放在不同的模式中，這樣它們就不會和其它物件的名字衝突。

建立模式 - CREATE SCHEMA 

view plaincopy to clipboardprint?

1. CREATE SCHEMA myschema;  

通過以上命令可以建立名字為 myschema 的模式，在該模式被建立後，其便可擁有自己的一組邏輯物件，如表、檢視和函式等。 

public 模式 
在介紹後面的內容之前，這裡我們需要先解釋一下public模式。每當我們建立一個新的資料庫時，PostgreSQL都會為我們自動建立該模式。當登入到該資料庫時，如果沒有特殊的指定，我們將以該模式 (public) 的形式操作各種資料物件，如： 

view plaincopy to clipboardprint?

1. CREATE TABLE products ( ... )   

等同: 

view plaincopy to clipboardprint?

1. CREATE TABLE public.products ( ... )   

許可權 
預設時，使用者看不到模式中不屬於他們所有的物件。為了讓他們看得見，模式的所有者需要在模式上賦予 USAGE 許可權。為了讓使用者使用模式中的物件，我們可能需要賦予額外的許可權，只要是適合該物件的。PostgreSQL 根據不同的物件提供了不同的許可權型別，如： 

view plaincopy to clipboardprint?

1. GRANT ALL ON SCHEMA myschema TO public;  

上面的 ALL 關鍵字將包含 CREATE 和 USAGE 兩種許可權。如果 public 模式擁有了 myschema 模式的 CREATE 許可權，那麼登入到該模式的使用者將可以在 myschema 模式中建立任意物件，如： 

view plaincopy to clipboardprint?

1. CREATE TABLE myschema.products (   
2.         product_no integer,   
3.         name text,   
4.         price numeric CHECK (price > 0),   
5.     );   

在為模式下的所有表賦予許可權時，需要將許可權拆分為各種不同的表操作，如： 

view plaincopy to clipboardprint?

1. ALTER DEFAULT PRIVILEGES IN SCHEMA myschema   
2.     GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES  TO public;   

在為模式下的所有 Sequence 序列物件賦予許可權時，需要將許可權拆分為各種不同的Sequence操作，如： 

view plaincopy to clipboardprint?

1. ALTER DEFAULT PRIVILEGES IN SCHEMA myschema   
2.     GRANT SELECT, UPDATE, USAGE ON SEQUENCES TO public;  

在為模式下的所有函式賦予許可權時，僅考慮執行許可權，如： 

view plaincopy to clipboardprint?

1. ALTER DEFAULT PRIVILEGES IN SCHEMA myschema   
2.     GRANT EXECUTE ON FUNCTIONS TO public;   

可以看出，通過以上方式在 public 模式下為 myschema 模式建立各種物件是極為不方便的。下面我們將要介紹另外一種方式，即通過role物件，直接登入並關聯到 myschema 物件，之後便可以在 myschema 模式下直接建立各種所需的物件了: 

view plaincopy to clipboardprint?

1. CREATE ROLE myschema LOGIN PASSWORD '123456';   --建立了和該模式關聯的角色物件。   
2.    CREATE SCHEMA myschema AUTHORIZATION myschema;   --將該模式關聯到指定的角色，模式名和角  

色名可以不相等。 在Linux Shell下，以myschema的角色登入到資料庫MyTest，在密碼輸入正確後將成功登入到該資料庫: 

$ psql -d MyTest -U myschema 
Password: 

MyTest=> CREATE TABLE test(i integer); 
CREATE TABLE 

MyTest=> \d --檢視該模式下，以及該模式有許可權看到的tables資訊列表。 

view plaincopy to clipboardprint?

1.           List of relations   
2. Schema     |   Name   | Type  |  Owner   
3. ------------+---------+------+----------   
4. myschema |   test     | table  | myschema   
5. (1 rows)   

刪除模式 - DROP SCHEMA 

view plaincopy to clipboardprint?

1. DROP SCHEMA myschema;  

如果要刪除模式及其所有物件，請使用級聯刪除： 

view plaincopy to clipboardprint?

1. DROP SCHEMA myschema CASCADE;  

模式搜尋路徑 
我們在使用一個數據庫物件時可以使用它的全稱來定位物件，然而這樣做往往也是非常繁瑣的，每次都不得不鍵入owner_name.object_name。PostgreSQL 中提供了 模式搜尋路徑，這有些類似於 Linux 中的 $PATH 環境變數，當我們執行一個 Shell 命令時，只有該命令位於$PATH的目錄列表中，我們才可以通過命令名直接執行，否則就需要輸入它的全路徑名。PostgreSQL 同樣也通過查詢一個搜尋路徑來判斷一個表究竟是哪個表，這個路徑是一個需要查詢的模式列表。在搜尋路徑裡找到的第一個表將被當作選定的表。如果在搜尋路徑中 沒有匹配表，那麼就報告一個錯誤，即使匹配表的名字在資料庫其它的模式中存在也如此。 

在搜尋路徑中的第一個模式叫做 當前模式。除了是搜尋的第一個模式之外，它還是在 CREATE TABLE 沒有宣告模式名的時候，新建表所屬於的模式。要顯示當前搜尋路徑，使用下面的命令： 

MyTest=> SHOW search_path; 

view plaincopy to clipboardprint?

1. search_path   
2. ---------------   
3. "$user",public   
4. 1 row)   

可以將新模式加入到搜尋路徑中，如: 

view plaincopy to clipboardprint?

1. SET search_path TO myschema,public;   

為搜尋路徑設定指定的模式，如： 

view plaincopy to clipboardprint?

1. SET search_path TO myschema; --當前搜尋路徑中將只是包含 myschema 一種模式。  

修改表的模式 

view plaincopy to clipboardprint?

1. alter table public.表名 set schema   新的模式名;  

[ 常見問題 ] How do I list all schemas in PostgreSQL?

Source From Here 
Question 
When using PostgreSQL v9.1, how do I list all of the schemas using SQL? 

How-To 
To lists all schemas, use the (ANSI) standard INFORMATION_SCHEMA: 

view plaincopy to clipboardprint?

1. select schema_name  
2. from information_schema.schemata  

More details in the manual: http://www.postgresql.org/docs/current/static/information-schema.html 

alternatively: 

view plaincopy to clipboardprint?

1. select nspname  
2. from pg_catalog.pg_namespace;  

More details about pg_catalog in the manual: http://www.postgresql.org/docs/current/static/catalogs.html 

Supplement 
* PostgreSQL Doc - Schemas 
* FAQ - How to select a schema in postgres when using psql? 

view plaincopy to clipboardprint?

1. # SET search_path TO myschema;  
2. # SET search_path TO myschema, public;  // want multiple schemas:  

[ Python 文章收集 ] SQLAlchemy Core - Using Functions (7)

Source From Here 
Using Functions 
Standard SQL has recommended many functions which are implemented by most dialects. They return a single value based on the arguments passed to it. Some SQL functions take columns as arguments whereas some are generic. The func keyword in SQLAlchemy API is used to generate these functions. 

In SQL, now() is a generic function. Following statements renders the now() function using func: 

>>> from sqlalchemy.sql import func 
>>> result = conn.execute(select([func.now()])) 
2019-03-24 08:26:56,296 INFO sqlalchemy.engine.base.Engine SELECT now() AS now_1 
2019-03-24 08:26:56,296 INFO sqlalchemy.engine.base.Engine {} 
>>> print(result.fetchone()) 
(datetime.datetime(2019, 3, 24, 0, 26, 56, 297582, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),)

On the other hand, count() function which returns number of rows selected from a table, is rendered by following usage of func: 

view plaincopy to clipboardprint?

1. from sqlalchemy.sql import func  
2. result = conn.execute(select([func.count(students.c.id)]))  
3. print (result.fetchone())  

From the above code, count of number of rows in students table will be fetched. 

Some built-in SQL functions are demonstrated using Employee table with following data: 

view plaincopy to clipboardprint?

1. testdb=# SELECT * FROM employee;  
2. id |   name    | marks  
3. ----+-----------+-------  
4.   1 | Mamal     |    56  
5.   2 | Fernandez |    85  
6.   3 | Sunil     |    62  
7.   4 | Bhaskar   |    76  
8. (4 rows)  

The max() function is implemented by following usage of func from SQLAlchemy which will result in 85, the total maximum marks obtained: 

view plaincopy to clipboardprint?

1. from sqlalchemy.sql import func  
2. result = conn.execute(select([func.min(employee.c.marks)]))  
3. print (result.fetchone())  

So, the AVG() function can also be implemented by using the below code: 

view plaincopy to clipboardprint?

1. from sqlalchemy.sql import func  
2. result = conn.execute(select([func.avg(employee.c.marks)]))  
3. print (result.fetchone())  

Functions are normally used in the columns clause of a select statement. They can also be given label as well as a type. A label to function allows the result to be targeted in a result row based on a string name, and a type is required when you need result-set processing to occur: 

>>> from sqlalchemy.sql import func 
>>> result = conn.execute(select([func.max(students.c.lastname).label('Name')])) 
2019-03-24 08:55:23,187 INFO sqlalchemy.engine.base.Engine SELECT max(students.lastname) AS "Name" 
FROM students 
2019-03-24 08:55:23,188 INFO sqlalchemy.engine.base.Engine {} 
>>> print(result.fetchone()) 
('Sattar',)

Using Set Operations 
Set operations such as UNION and INTERSECT are supported by standard SQL and most of its dialect. SQLAlchemy implements them with the help of following functions. 

union() 
While combining results of two or more SELECT statements, UNION eliminates duplicates from the resultset. The number of columns and datatype must be same in both the tables. 

The union() function returns a CompoundSelect object from multiple tables. Following example demonstrates its use: 
- demo13.py 

view plaincopy to clipboardprint?

1. #!/usr/bin/env python36  
2. from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, union  
3.   
4. db_string = "postgresql://postgres:<yourpassword>@localhost/testdb"  
5. engine = create_engine(db_string, echo = True)  
6. meta = MetaData(bind=engine)  
7. meta.reflect()  
8. addresses = meta.tables['addresses']  
9. conn = engine.connect()  
10.   
11. u = union(addresses.select().where(addresses.c.email_add.like('%@gmail.com')),  
12.           addresses.select().where(addresses.c.email_add.like('%@yahoo.com')))  
13.   
14. result = conn.execute(u)  
15. print('Total {:,d} row(s):'.format(result.rowcount))  
16. for r in result.fetchall():  
17.     print("\t{}".format(r))  

Output: 

view plaincopy to clipboardprint?

1. ...  
2. 2019-03-24 09:07:28,603 INFO sqlalchemy.engine.base.Engine SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add  
3. FROM addresses  
4. WHERE addresses.email_add LIKE %(email_add_1)s UNION SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add  
5. FROM addresses  
6. WHERE addresses.email_add LIKE %(email_add_2)s  
7. 2019-03-24 09:07:28,603 INFO sqlalchemy.engine.base.Engine {'email_add_1': '%@gmail.com', 'email_add_2': '%@yahoo.com'}  
8. Total 8 row(s):  
9.         (1, 1, 'Shivajinagar Pune', '[email protected]')  
10.         (2, 1, 'ChurchGate Mumbai', '[email protected]')  
11.         (3, 3, 'Jubilee Hills Hyderabad', '[email protected]')  
12.         (4, 5, 'MG Road Bangaluru', '[email protected]')  
13.         (6, 1, 'Shivajinagar Pune', '[email protected]')  
14.         (7, 1, 'ChurchGate Mumbai', '[email protected]')  
15.         (8, 3, 'Jubilee Hills Hyderabad', '[email protected]')  
16.         (9, 5, 'MG Road Bangaluru', '[email protected]')  

union_all() 
UNION ALL operation cannot remove the duplicates and cannot sort the data in the resultset. For example, in above query, UNION is replaced by UNION ALL to see the effect: 

view plaincopy to clipboardprint?

1. u = union_all(addresses.select().where(addresses.c.email_add.like('%@gmail.com')),  
2.           addresses.select().where(addresses.c.email_add.like('%@yahoo.com')))  

Output: 

view plaincopy to clipboardprint?

1. ...  
2. 2019-03-24 09:16:57,046 INFO sqlalchemy.engine.base.Engine SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add  
3. FROM addresses  
4. WHERE addresses.email_add LIKE %(email_add_1)s UNION ALL SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add  
5. FROM addresses  
6. WHERE addresses.email_add LIKE %(email_add_2)s  
7. 2019-03-24 09:16:57,046 INFO sqlalchemy.engine.base.Engine {'email_add_1': '%@gmail.com', 'email_add_2': '%@yahoo.com'}  
8. Total 8 row(s):  
9.         (1, 1, 'Shivajinagar Pune', '[email protected]')  
10.         (2, 1, 'ChurchGate Mumbai', '[email protected]')  
11.         (3, 3, 'Jubilee Hills Hyderabad', '[email protected]')  
12.         (6, 1, 'Shivajinagar Pune', '[email protected]')  
13.         (7, 1, 'ChurchGate Mumbai', '[email protected]')  
14.         (8, 3, 'Jubilee Hills Hyderabad', '[email protected]')  
15.         (4, 5, 'MG Road Bangaluru', '[email protected]')  
16.         (9, 5, 'MG Road Bangaluru', '[email protected]')  

If you want to get an unique email address result, try below code: 
- demo14.py 

view plaincopy to clipboardprint?

1. #!/usr/bin/env python36  
2. from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, union, union_all  
3. from sqlalchemy.sql import select  
4.   
5. db_string = "postgresql://postgres:<yourpassword>@localhost/testdb"  
6. engine = create_engine(db_string, echo = True)  
7. meta = MetaData(bind=engine)  
8. meta.reflect()  
9. addresses = meta.tables['addresses']  
10. conn = engine.connect()  
11.   
12. u = union(select([addresses.c.email_add]).where(addresses.c.email_add.like('%@gmail.com')),  
13.           select([addresses.c.email_add]).where(addresses.c.email_add.like('%@yahoo.com')))  
14.   
15. u.order_by(u.c.email_add)  
16.   
17. result = conn.execute(u)  
18. print('Total {:,d} row(s):'.format(result.rowcount))  
19. for r in result.fetchall():  
20.     print("\t{}".format(r))  

Output: 

view plaincopy to clipboardprint?

1. 2019-03-24 09:35:40,909 INFO sqlalchemy.engine.base.Engine SELECT addresses.email_add  
2. FROM addresses  
3. WHERE addresses.email_add LIKE %(email_add_1)s UNION SELECT addresses.email_add  
4. FROM addresses  
5. WHERE addresses.email_add LIKE %(email_add_2)s  
6. 2019-03-24 09:35:40,909 INFO sqlalchemy.engine.base.Engine {'email_add_1': '%@gmail.com', 'email_add_2': '%@yahoo.com'}  
7. Total 4 row(s):  
8.         ('[email protected]',)  
9.         ('[email protected]',)  
10.         ('[email protected]',)  
11.         ('[email protected]',)  

except_() 
The SQL EXCEPT clause/operator is used to combine two SELECT statements and return rows from the first SELECT statement that are not returned by the second SELECT statement. The except_() function generates a SELECT expression with EXCEPT clause. 

In the following example, the except_() function returns only those records from addresses table that have ‘gmail.com’ in email_add field but excludes those which have ‘Pune’ as part of postal_add field: 

view plaincopy to clipboardprint?

1. u = except_(select([addresses.c.email_add, addresses.c.postal_add]).where(addresses.c.email_add.like('%@gmail.com')),  
2.             select([addresses.c.email_add, addresses.c.postal_add]).where(addresses.c.postal_add.like('%Pune')))  

Result of the above code is the following SQL expression: 

view plaincopy to clipboardprint?

1. ...  
2. 2019-03-24 09:42:55,989 INFO sqlalchemy.engine.base.Engine SELECT addresses.email_add, addresses.postal_add  
3. FROM addresses  
4. WHERE addresses.email_add LIKE %(email_add_1)s EXCEPT SELECT addresses.email_add, addresses.postal_add  
5. FROM addresses  
6. WHERE addresses.postal_add LIKE %(postal_add_1)s  
7. 2019-03-24 09:42:55,989 INFO sqlalchemy.engine.base.Engine {'email_add_1': '%@gmail.com', 'postal_add_1': '%Pune'}  
8. Total 2 row(s):  
9.         ('[email protected]', 'ChurchGate Mumbai')  
10.         ('[email protected]', 'Jubilee Hills Hyderabad')  

intersect() 
Using INTERSECT operator, SQL displays common rows from both the SELECT statements. The intersect() function implements this behaviour. 

In following examples, two SELECT constructs are parameters to intersect() function. One returns rows containing ‘gmail.com’ as part of email_add column, and other returns rows having ‘Pune’ as part of postal_add column. The result will be common rows from both resultsets: 
- demo16.py 

view plaincopy to clipboardprint?

1. #!/usr/bin/env python36  
2. from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, union, union_all, except_, intersect  
3. from sqlalchemy.sql import select  
4.   
5. db_string = "postgresql://postgres:<yourpassword>@localhost/testdb"  
6. engine = create_engine(db_string, echo = True)  
7. meta = MetaData(bind=engine)  
8. meta.reflect()  
9. addresses = meta.tables['addresses']  
10. conn = engine.connect()  
11.   
12. u = intersect(select([addresses.c.email_add, addresses.c.postal_add]).where(addresses.c.email_add.like('%@gmail.com')),  
13.               select([addresses.c.email_add, addresses.c.postal_add]).where(addresses.c.postal_add.like('%Pune')))  
14.   
15.   
16. result = conn.execute(u)  
17. print('Total {:,d} row(s):'.format(result.rowcount))  
18. for r in result.fetchall():  
19.     print("\t{}".format(r))  

Output: 

view plaincopy to clipboardprint?

1. ...  
2. 2019-03-24 09:48:22,347 INFO sqlalchemy.engine.base.Engine SELECT addresses.email_add, addresses.postal_add  
3. FROM addresses  
4. WHERE addresses.email_add LIKE %(email_add_1)s INTERSECT SELECT addresses.email_add, addresses.postal_add  
5. FROM addresses  
6. WHERE addresses.postal_add LIKE %(postal_add_1)s  
7. 2019-03-24 09:48:22,347 INFO sqlalchemy.engine.base.Engine {'email_add_1': '%@gmail.com', 'postal_add_1': '%Pune'}  
8. Total 1 row(s):  
9.         ('[email protected]', 'Shivajinagar Pune')