Olá, Habr! Convidamos você para uma aula de demonstração gratuita "CockroachDB Parallel Cluster" , que será realizada como parte do curso "PostgreSQL". Também estamos publicando a tradução de um artigo de Tom Brown - Engenheiro de Sistemas Principal da EnterpriseDB.
Neste artigo, veremos algumas dicas úteis para trabalhar com PostgreSQL:
Link para toda a linha
Comparando várias colunas
Expressões de tabela comuns
Opções de configuração personalizadas
Comparação de valores booleanos sem "igual"
Altere o tipo de coluna sem custo extra
Informações sobre a seção em que a string está
Tabelas são tipos
Link para toda a linha
Você já tentou uma consulta como essa?
SELECT my_table FROM my_table;O pedido parece estranho. Ele retorna todas as colunas da tabela como uma coluna. Por que você precisa disso? Bem, acho que você referenciou tabelas da seguinte maneira mais de uma vez:
SELECT table_a.column, table_b.column
FROM table_a
INNER JOIN table_b ON table_a.id = table_b.aid;Há uma referência de linha aqui, mas apenas uma coluna. E não há nada incomum aqui. Que tal agora?
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», « ».