¡Hola, Habr! Lo invitamos a una lección de demostración gratuita "CockroachDB Parallel Cluster" , que se llevará a cabo como parte del curso "PostgreSQL". También estamos publicando una traducción del artículo de Tom Brown - Ingeniero principal de sistemas en EnterpriseDB.
En este artículo, veremos algunos consejos útiles para trabajar con PostgreSQL:
Enlace a toda la línea
Comparando varias columnas
Expresiones de tabla comunes
Opciones de configuración personalizadas
Comparación de valores booleanos sin "iguales"
Cambiar el tipo de columna sin costo adicional
Información sobre la sección en la que se encuentra la cadena
Las tablas son tipos
Enlace a toda la línea
¿Has probado alguna vez una consulta como esta?
SELECT my_table FROM my_table;La solicitud parece extraña. Devuelve todas las columnas de una tabla como una sola columna. ¿Por qué podrías necesitarlo? Bueno, creo que ha hecho referencia a tablas de la siguiente manera más de una vez:
SELECT table_a.column, table_b.column
FROM table_a
INNER JOIN table_b ON table_a.id = table_b.aid;Aquí hay una referencia de fila, pero solo una columna. Y no hay nada inusual aquí. ¿Qué tal esto?
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», « ».