你可能不知道PostgreSQL可以做的8件有趣的事情!
1
整行引用
您是否尝试过运行以下语句？
SELECT my_table FROM my_table;这可能看起来很奇怪，但它所做的是将所有列作为行类型返回到单个列中。
现在你为什么要这样做？好吧，您很可能已经以这种方式引用了该表：
SELECT table_a.column, table_b.columnFROM table_aINNER JOIN table_b ON table_a.id = table_b.aid;它的作用是引用整行，然后只要求该集合中的一列。但这只是常见的情况。这个怎么样？
SELECT pgccc, backup_pgcccFROM pgcccFULL JOIN backup_pgccc ON pgccc = backup_pgcccWHERE pgccc IS NULL OR backup_pgccc IS NULL;这里我们有一个表和它的备份。如果我们想查看它们之间的差异，或者是因为我们想查看自备份以来发生了什么变化，或者想查看我们是否丢失了可能需要从备份中恢复的任何历史行，该怎么办？
作为快速演示，我们将创建表并插入三行：
postgres=# CREATE TABLE pgccc (id serial, person text, country text);CREATE TABLEpostgres=# insert into pgccc (person, country) values ('Tim','France'),('Dieter','Germany'),('Marcus','Finland');INSERT 3postgres=# CREATE TABLE backup_pgccc (id serial, person text, country text);CREATE TABLEpostgres=# insert into backup_pgccc select * from pgccc;INSERT 3postgres=# select * from backup_pgccc; id | person | country ----+--------+--------- 1 | Tim | France 2 | Dieter | Germany 3 | Marcus | Finland(3 rows)我们希望表有所不同，因此我们将从原始表中删除一行并将新行添加到备份表中：
postgres=# delete from pgccc where id = 2 ;DELETE 1postgres=# insert into pgccc (person, cou)
postgres=# insert into pgccc (person, country) values ('Roberto','Italy');INSERT 1
postgres=# select * from pgccc; id | person | country ----+---------+--------- 1 | Tim | France 3 | Marcus | Finland 4 | Roberto | Italy(3 rows)后，让我们看看如果我们运行查询来比较表会发生什么：
postgres=# SELECT pgccc, backup_pgcccpostgres-# FROM pgcccpostgres-# FULL JOIN backup_pgccc ON pgccc = backup_pgcccpostgres-# WHERE pgccc IS NULL OR backup_pgccc IS NULL; pgccc | backup_pgccc -------------------+-------------------- (4,Roberto,Italy) | | (2,Dieter,Germany)(2 rows)我们可以在这里看到 backup_pgccc 表包含数据表中缺少的行，反之亦然。
还有这个：
postgres=# select to_jsonb(pgccc) from pgccc; to_jsonb ----------------------------------------------------- {"id": 1, "person": "Tim", "country": "France"} {"id": 3, "person": "Marcus", "country": "Finland"} {"id": 4, "person": "Roberto", "country": "Italy"}(3 rows)我们刚刚将所有数据转换为 JSON！
2
比较选择的列
假设我们有以下查询：
SELECT country, company, department
FROM pgccc
WHERE country = 'Australia'
AND company = 'Skynet'
AND department = 'Robotics';我们可以摆脱那些 AND：
SELECT country, company, department
FROM pgccc
WHERE (country, company, department) = ('Australia','Skynet','Robotics');我们还可以使用 IN 来满足 OR 条件。如果我们调整原始查询：
SELECT country, company, department
FROM pgccc
WHERE department = 'Robotics'
AND (
(country = 'Australia'
AND company = 'Skynet')
OR
(country = 'Norway'
AND company = 'Nortech')
);我们可以将其缩短为
SELECT country, company, department
FROM pgccc
WHERE department = 'Robotics'
AND (country, company) IN (('Australia','Skynet'),('Norway','Nortech'));3
硬编码表
假设您只有对数据库及其表的读取权限，但您有一小部分数据要用于连接现有表。
SELECT station, time_recorded, temperature
FROM pgccc_weather_stations;
station | time_recorded | temperature
----------------+---------------------+-------------
Biggin_Hill_14 | 2022-04-02 14:02:44 | 22.4
Reigate_03 | 2022-04-02 17:05:12 | 20.9
Aberdeen_06 | 2022-04-02 18:52:49 | 8.5
Madrid_05 | 2022-04-02 15:05:27 | 30.1
rows)我们想知道每个车站的温度有多高或有多冷，所以我们可以构造这个查询：
SELECT station,
CASE
WHEN temperature <= 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 pgccc_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 pgccc_weather_stations ws
INNER JOIN temp_ranges tr ON ws.temperature <@ tr.temp_range;注意：那些不熟悉范围类型的人可能会被“numrange”值和数据类型混淆。这是几种范围类型中的一种，这是一种用于数值范围的类型。圆括号表示排他；方括号表示包括在内。因此，'(0,10]' 表示“从但不包括 0，直到并包括 10”。缺失值表示之前的任何值，如果它是个值，或者之后的任何值，如果是第二个值。
4
自定义配置参数
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 中配置它，您需要将该扩展的类添加到列表中。
但是，此要求在较新的版本中已删除，您不再需要显式声明它们。只有内置的配置参数没有前缀，所以任何自定义的都需要前缀，否则将不被接受。
正如您在上面的示例中所看到的，当您想要提供有关集群的某种元数据时，这可能会很方便。
5
布尔值可以独立存在
您可能编写了如下查询：
SELECT user, location, active
FROM pger
WHERE active = true;你知道你不需要那个“= true”吗？你可以这样写：
WHERE active这是因为布尔值不需要与另一个布尔值进行比较，因为表达式无论如何都会返回 true 或 false。如果你想表明相反的，你可以写：
WHERE NOT active读起来也更好。
6
免费转换列数据类型
通常，当更改包含现有数据的表列的类型时，必须重写整个表以将数据存储在其新数据类型中。但在很多情况下，这不会发生。
以下是如何找到所有这些：
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;该语句将返回一个相对较小的类型列表以及它们可以转换为的类型集，因为它们是“二进制兼容的”。在此列表中，您将看到 text、xml、char 和 varchar 都是可以互换的——就二进制格式而言。因此，如果您在文本列中有一个包含 XML 数据的表，请随意转换它而不会受到惩罚（请注意，如果您的数据中有的 XML，Postgres 将禁止它并告诉您）。
7
查找哪些行属于哪个基础分区
您可能将数据拆分为不同的分区，但是当您选择行时，如果您想知道每行来自哪个分区表怎么办？这很简单：只需将 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 类型，它将返回表名。
8
表是类型
是的，你没听错。每当您创建一个表时，您也在有效地创建一个新类型。你自己看：
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).rrpFROM personal_favourites;现在我知道您在想什么：包含包含类型的表类型的表是否也是类型？好吧，是的，但我们不要进行演示，否则我们终会陷入令人困惑的 Inception 风格的境地。
而且，正如我在“整行引用”中提到的，您可以将整行转换为 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_favouritesSET 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" + } + ] + }账面价值现在包含一个账本对象数组，并且我们的查询没有任何更改。
作者原文地址
https://www.enterprisedb.com/blog/8-cool-interesting-facts-things-postgresql-can-do
相关文章