Bonjour, Habr! Nous vous invitons à une leçon de démonstration gratuite "CockroachDB Parallel Cluster" , qui se tiendra dans le cadre du cours "PostgreSQL". Nous publions également une traduction de l'article de Tom Brown - Principal Systems Engineer chez EnterpriseDB.
Dans cet article, nous examinerons quelques conseils utiles pour travailler avec PostgreSQL:
Lien vers toute la ligne
Comparaison de plusieurs colonnes
Expressions de table courantes
Options de configuration personnalisées
Comparaison des valeurs booléennes sans "égal"
Changer le type de colonne sans frais supplémentaires
Informations sur la section dans laquelle se trouve la chaîne
Les tableaux sont des types
Lien vers toute la ligne
Avez-vous déjà essayé une requête comme celle-ci?
SELECT my_table FROM my_table;La demande semble étrange. Il renvoie toutes les colonnes du tableau sous forme d'une seule colonne. Pourquoi pourriez-vous en avoir besoin? Eh bien, je pense que vous avez référencé des tables de la manière suivante plusieurs fois:
SELECT table_a.column, table_b.column
FROM table_a
INNER JOIN table_b ON table_a.id = table_b.aid;Il y a une référence de ligne ici, mais une seule colonne. Et il n'y a rien d'inhabituel ici. Que dis-tu de ça?
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», « ».