哈Ha！ 我们邀请您参加免费的模拟课程“ CockroachDB并行集群”，该课程将作为“ PostgreSQL”课程的一部分进行。我们还将发布EnterpriseDB首席系统工程师Tom Brown的文章翻译。
在本文中，我们将介绍一些使用PostgreSQL的有用技巧：
链接到整行
比较多列
常用表表达式
自定义配置选项
不带``等于''的布尔值比较
无需额外费用即可更改列类型
有关字符串所在部分的信息
表是类型
链接到整行
您曾经尝试过这样的查询吗？
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;data backup_data. , : - ?
:
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 , 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!
, .
, :
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'));, . , (join) .
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;. , - (CTE, common table expression):
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), pgsettings SHOW ALL.
? PostgreSQL 9.2 customvariable_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;, " " . , text, xml, char varchar . , XML- text, ( , 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 - , SELECT. OID (Object Identifier) , . 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));, :
SELECT (book).isbn, (book).title, (book).rrp
FROM 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];:
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" +
} +
] +
}- . , Postgres ! , 5 Ways to Get More from PostgreSQL ( PostgreSQL).
? Demo- « PostgreSQL» - — «PostgreSQL» « », «Software Architect», «MS SQL Server Developer», « ».