原文標題：8 Fascinating Things You Probably Didn’t Know PostgreSQL Can Do!
原文地址：https://www.enterprisedb.com/blog/8-cool-interesting-facts-things-postgresql-can-do
原文作者：thom-brown
翻譯：多米爸比
本文為在Postgres中執行任務提供有用的提示和快捷方式。
- 整行引用
- where比較列合并
- With硬編碼表
- 自定義配置參數
- 布爾值列可以獨立寫
- 不費代價轉換列數據類型
- 查找數據行的歸屬分區
- 表即類型
一、整行引用
您是否嘗試過運行以下語句？
SELECT my_table FROM my_table;
這看起來可能很奇怪，但它所做的是將所有列作為行類型返回單列。您為什么要這樣做？好吧，您很可能已經以這種方式引用了該表：
SELECT table_a.column, table_b.column
FROM table_a
INNER JOIN table_b ON table_a.id = table_b.aid;
它的作用是引用整行，然后只要求單列顯示每個表，這只是常見的情況。
再看下面這個示例：
SELECT data, backup_data
FROM data
FULL JOIN backup_data ON data = backup_data
WHERE data IS NULL OR backup_data IS NULL;
這里我們有一個表和它的備份。如果我們想查看它們之間的差異，或者是因為我們想查看自備份以來發生了什么變化，或者想查看我們是否丟失了任何可能需要從備份中恢復的歷史行，該怎么辦？
為快速演示，我們將創建表并插入三行：
postgres=# CREATE TABLE data (id serial, person text, country text);
CREATE TABLE
postgres=# INSERT INTO data (person, country)
VALUES ('Tim','France'),('Dieter','Germany'),('Marcus','Finland');
INSERT 0 3
現在，讓我們創建一個相同的表副本并將相同的數據復制到其中：
postgres=# CREATE TABLE backup_data (id serial, person text, country text);
CREATE TABLE
postgres=# INSERT INTO backup_data SELECT * FROM data;
INSERT 0 3
我們希望兩張表數據有所不同，因此我們從原始表中刪除一行并添加新行到備份表中：
postgres=# DELETE FROM data WHERE id = 2;
DELETE 1
postgres=# INSERT INTO data (person, country)
VALUES ('Roberto','Italy');
INSERT 0 1
最后，讓我們看看如果我們運行查詢來比較表會發生什么：
postgres=# SELECT data, backup_data
postgres-# FROM data
postgres-# FULL JOIN backup_data ON data = backup_data
postgres-# WHERE data IS NULL OR backup_data IS NULL;
data | backup_data
-------------------+--------------------
| (2,Dieter,Germany)
(4,Roberto,Italy) |
(2 rows)
我們可以在這里看到backup_data表包含數據表中缺少的行，反之亦然。
還有這個：
postgres=# SELECT to_jsonb(data) FROM data;
to_jsonb
-----------------------------------------------------
{"id": 1, "person": "Tim", "country": "France"}
{"id": 3, "person": "Marcus", "country": "Finland"}
{"id": 4, "person": "Roberto", "country": "Italy"}
(3 rows)
我們將所有數據轉換為JSON！
二、where比較列合并
這是一個非常方便的技巧，可使查詢更短且更易于閱讀。
假設我們有以下查詢：
SELECT country, company, department
FROM suppliers
WHERE country = 'Australia'
AND company = 'Skynet'
AND department = 'Robotics';
我們可以去掉那些AND：
SELECT country, company, department
FROM suppliers
WHERE (country, company, department) = ('Australia','Skynet','Robotics');
我們還可以使用IN來滿足OR條件。如果我們調整原始查詢：
SELECT country, company, department
FROM suppliers
WHERE department = 'Robotics'
AND (
(country = 'Australia'
AND company = 'Skynet')
OR
(country = 'Norway'
AND company = 'Nortech')
);
我們可以將其縮短為：
SELECT country, company, department
FROM suppliers
WHERE department = 'Robotics'
AND (country, company) IN (('Australia','Skynet'),('Norway','Nortech'));
三、With硬編碼表
假設您只有對數據庫及其表的讀取權限，但您有一小部分數據要用于連接現有表。
SELECT station, time_recorded, temperature
FROM weather_stations;
station | time_recorded | temperature
----------------+---------------------+-------------
Biggin_Hill_14 | 2020-02-02 13:02:44 | 22.4
Reigate_03 | 2020-02-02 16:05:12 | 20.9
Aberdeen_06 | 2020-02-02 15:52:49 | 8.5
Madrid_05 | 2020-02-02 14:05:27 | 30.1
(4 rows)
我們想知道每個車站的溫暖程度，所以我們可以構造這個查詢：
SELECT station,
CASE
WHEN temperature <= 0 THEN 'freezing'
WHEN temperature < 10 THEN 'cold'
WHEN temperature < 18 THEN 'mild'
WHEN temperature < 30 THEN 'warm'
WHEN temperature < 36 THEN 'hot'
WHEN temperature >= 36 THEN 'scorching'
END AS temp_feels
FROM weather_stations;
如果我們設置一個偽表來包含所有這些信息，我們可以添加更多模擬的數據，并且為了更容易使用，我們可以將它放在一個公共表表達式中：
WITH temp_ranges (temp_range, feeling, colour) AS (
VALUES
('(,0]'::numrange, 'freezing', 'blue'),
('( 0,10)'::numrange, 'cold', 'white'),
('[10,18)'::numrange, 'mild', 'yellow'),
('[18,30)'::numrange, 'warm', 'orange'),
('[30,36)'::numrange, 'hot', 'red'),
('[36,)'::numrange, 'scorching', 'black')
)
SELECT ws.station, tr.feeling, tr.colour
FROM weather_stations ws
INNER JOIN temp_ranges tr ON ws.temperature <@ tr.temp_range;
注意：不熟悉范圍類型的人可能會被“numrange”值和數據類型混淆。它是范圍類型中的一種，用于數值范圍的類型。圓括號表示排他；方括號表示包括在內。因此，’(0,10]’ 表示“從0開始但不包括0，直到并包括10”。缺失值如果是第一個值表示之前的任何值，缺失值如果是第二個值表示之后的任何值。
四、自定義配置參數
Postgres有廣泛的參數，允許您配置數據庫系統的各個方面，但您也可以添加自己的參數并調用任何您想要的參數，只要您給它們自己的配置類。
例如，您可以將其添加到 postgresql.conf：
config.cluster_type = 'staging'
然后使用 SHOW 命令訪問它。
postgres=# SHOW config.cluster_type;
config.cluster_type
---------------------
staging
(1 row)
請注意，這些設置不會出現在pg_settings中，也不會由SHOW ALL輸出。
那么為什么我們能夠做到這一點呢？為什么我們不能在不提供配置前綴的情況下做到這一點？在 PostgreSQL 9.2 之前，有一個名為custom_variable_classes的設置，它獲取了一個類列表，這些類可以被擴展用于他們自己的設置。如果您想在postgresql.conf 中配置它，您需要將該擴展的類添加到列表中。但是，此要求在較新的版本中已刪除，您不再需要顯式聲明它們。只有內置的配置參數沒有前綴，所以任何自定義的參數都需要前綴，否則將不被接受。
正如您在上面的示例中所看到的，當您想要提供有關集群的某種元數據時，這可能會很方便。
五、布爾值列可以獨立寫
您可能編寫了如下查詢：
SELECT user, location, active
FROM subscriptions
WHERE active = true;
您知道您不需要那個“= true”嗎？可以這樣寫：
WHERE active
這是因為布爾值不需要與另一個布爾值進行比較，因為表達式無論如何都會返回 true 或 false。如果想取反的，可以寫：
WHERE NOT active
讀起來也更好。
六、不費代價轉換列數據類型
通常，當更改包含現有數據表列的類型時，必須重寫整個表以將數據存儲在其新數據類型中。但在很多情況下，可以避免這一情況發生。
以下語句是如何找到所有這些：
SELECT
castsource::regtype::text,
array_agg(casttarget::regtype ORDER BY casttarget::regtype::text) casttargets
FROM pg_cast
WHERE castmethod = 'b'
GROUP BY 1
ORDER BY 1;
該語句將返回一個相對較小的類型列表以及它們可以轉換為的類型集，因為它們是“binary compatible”(二進制親和的)。在此列表中，您將看到text、xml、char 和 varchar都是可以互換的–它們是相同存儲的二進制格式。因此，如果您在文本列中有一個包含XML數據的表，請隨意轉換它而不會受到影響（請注意，如果您的數據中有無效的XML，Postgres將禁止它并告訴您）。
七、查找數據行的歸屬分區
您可能將數據拆分為不同的分區，但是當您選擇行時，如果您想知道每行來自哪個分區表怎么辦？這很簡單：只需將 tableoid::regclass 添加到您的 SELECT 子句中。例如：
postgres=# SELECT tableoid::regclass, * FROM customers;
tableoid | id | name | country | subscribed
--------------+-----+----------------+----------------+------------
customers_de | 23 | Hilda Schumer | Germany | t
customers_uk | 432 | Geoff Branshaw | United Kingdom | t
customers_us | 815 | Brad Moony | USA | t
(3 rows)
這是有效的，因為tableoid 是一個隱藏的系統列，您只需要顯式選擇才能看到它。它返回該行所屬表的 OID（對象標識符）。如果將其轉換為regclass 類型，它將返回表名。
八、表即類型
您沒聽錯。每當創建一個表時，您也同時創建了一個新類型：
CREATE TABLE books (isbn text, title text, rrp numeric(10,2));
我們可以在創建另一個表時使用此表類型，或者作為函數參數或返回類型：
CREATE TABLE personal_favourites (book books, movie movies, song songs);
然后，您將在其中輸入信息：
INSERT INTO personal_favourites (book)
VALUES (('0756404746','The Name of the Wind',9.99));
或者：
INSERT INTO personal_favourites (book.isbn, book.title, book.rrp)
VALUES ('0756404746','The Name of the Wind',9.99);
要從表值中獲取單個值，您可以從列中選擇列：
SELECT (book).isbn, (book).title, (book).rrp
FROM personal_favourites;
而且，正如我在“整行引用”中提到的，您可以將整行轉換為JSON，它會以您希望的方式返回所有內容：
postgres=# SELECT jsonb_pretty(to_jsonb(personal_favourites))
FROM personal_favourites;
jsonb_pretty
----------------------------------------------
{ +
"book": { +
"rrp": 9.99, +
"isbn": "0756404746", +
"title": "The Name of the Wind" +
}, +
"song": { +
"album": "Grace", +
"title": "This is our Last Goodbye",+
"artist": "Jeff Buckley" +
}, +
"movie": { +
"title": "Magnolia", +
"studio": "New Line Cinema", +
"release_date": "2000-03-24" +
} +
}
可以使用這種功能為JSON 數據創建模式，以實現類似NoSQL 的功能，但數據具有已定義的結構。
但是等等，如果我想存儲和查詢所有我最喜歡的書籍、歌曲和電影，而不僅僅是一個呢？
這也可以。任何類型，包括表，都可以通過在數據類型名稱后添加 [] 轉換為數組。與其重新創建表，不如將列轉換為數組類型，然后添加另一本書：
ALTER TABLE personal_favourites
ALTER COLUMN book TYPE books[] USING ARRAY[book];
ALTER TABLE personal_favourites
ALTER COLUMN movie TYPE movies[] USING ARRAY[movie];
ALTER TABLE personal_favourites
ALTER COLUMN song TYPE songs[] USING ARRAY[song];
我們將在 book 數組中添加另一本書：
UPDATE personal_favourites
SET book = book || ('1408891468','Jonathan Strange and Mr Norrell',7.99)::books;
現在我們的結果如下所示：
postgres=# SELECT jsonb_pretty(to_jsonb(personal_favourites))
FROM personal_favourites;
jsonb_pretty
--------------------------------------------------------
{ +
"book": [ +
{ +
"rrp": 9.99, +
"isbn": "0756404746", +
"title": "The Name of the Wind" +
}, +
{ +
"rrp": 7.99, +
"isbn": "1408891468", +
"title": "Jonathan Strange and Mr Norrell"+
} +
], +
"song": [ +
{ +
"album": "Grace", +
"title": "This is our Last Goodbye", +
"artist": "Jeff Buckley" +
} +
], +
"movie": [ +
{ +
"title": "Magnolia", +
"studio": "New Line Cinema", +
"release_date": "2000-03-24" +
} +
] +
}
book的值現在包含book對象數組，并且我們的查詢沒有任何更改。
我希望這些技巧可以幫助您從Postgres 中獲得更多價值！
保持聯系
從2019年12月開始寫第一篇文章，分享的初心一直在堅持，本人現在組建了一個PG樂知樂享交流群，歡迎關注我文章的小伙伴進群吹牛嘮嗑，交流技術，互贊文章。
如果群二維碼失效可以加我微信。
