PostgreSQL là cơ sở dữ liệu quan hệ hướng đối tượng

30102015

1. pgAdmin III – GUI cho PostgreSQL

Cài đặt trên Ubuntu bằng lệnh:

$ sudo apt-get install pgadmin3

1

2. Mô hình dữ liệu

PostgreSQL không chỉ là cơ sở dữ liệu quan hệ, nó là quan hệ hướng đối tượng. Điều này cung cấp cho nó một vài lợi thế so với các cơ sở dữ liệu SQL mã nguồn mở khác như MySQL, MariaDB và Firebird.
Một đặc điểm cơ bản của cơ sở dữ liệu quan hệ hướng đối tượng là hỗ trợ các đối tượng người dùng tự định nghĩa và các hành vi của chúng bao gồm các kiểu dữ liệu, các hàm, các thao tác, các tên miền và các chỉ mục. Điều này làm cho PostgreSQL cực kỳ mạnh và linh hoạt. Trong những cái khác, các cấu trúc dữ liệu phức tạp có thể được tạo ra, lưu trữ, và truy xuất. Trong một vài ví dụ ở bên dưới bạn sẽ nhìn thấy các cấu trúc hỗn hợp lồng nhau cái mà các hệ quản trị cơ sở dữ liệu chuẩn (RDBMS) không hỗ trợ.

3. Các kiểu dữ liệu và cấu trúc

Có một danh sách các kiểu dữ liệu PostgreSQL hỗ trợ. Bên cạnh kiểu số, floating-point, chuỗi, boolean, và các kiểu dữ liệu mà bạn mong muốn, PostgreSQL tự hào với uuid, tiền tệ, liệt kê (enumerated), nhị phân (binary), địa chỉ mạng, chuỗi bit, tìm kiếm văn bản, xml, mảng, hình học (geometric), json, hỗn hợp, các loại khoảng (range types), cũng như một vài kiểu internal cho nhận biết đối tượng và vị trí đăng nhập. Để công bằng, MySQL, MariaDB và Firebird mỗi cái có một vài loại ở mức độ khác nhau, nhưng PostgreSQL hỗ trợ tất cả.

Chúng ta hãy xem xét một vài thứ:

Địa chỉ mạng

PostgreSQL cung cấp nhiều kiểu dữ liệu dành cho việc lưu trữ địa chỉ mạng. Kiểu dữ liệu CIDR (Classless Internet Domain Routing) theo quy ước cho địa chỉ mạng IPv4 và IPv6. Một vài ví dụ cho CIDR:

  • 92.168.100.128/25
  • 10.1.2.3/32
  • 2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128
  • ::ffff:1.2.3.0/128

Cũng có sẵn cho việc lưu trữ địa chỉ mạng là kiểu dữ liệu INET, sử dụng cho lưu trữ IPv4 và IPv6 nơi subnet là không bắt buộc.

Kiểu dữ liệu MACADDR có thể sử dụng lưu trữ địa chỉ MAC cho việc xác định phần cứng chẳng hạn như 08-00-2b-01-02-03.

MySQL và MariaDB có một vài hàm INET để chuyển đổi địa chỉ mạng, nhưng không cung cấp các kiểu dữ liệu cho việc lưu trữ các địa chỉ mạng. Firebird cũng không có kiểu địa chỉ mạng.

Mảng nhiều chiều

Bởi vì PostgreSQL là một cơ sở dữ liệu quan hệ hướng đối tượng, mảng các giá trị có thể lưu trữ hầu hết các kiểu dữ liệu có sẵn. Làm điều này bằng cách thêm cặp ngoặc [ ] vào sau kiểu dữ liệu chỉ định cho cột hoặc sử dụng ARRAY. Kích thước của mảng có thể chỉ định cụ thể, nhưng không bắt buộc. Hãy xem thực đơn của một kỳ nghỉ dã ngoại để minh họa cho việc sử dụng mảng:

— create a table where the values are arrays
CREATE TABLE holiday_picnic (
holiday varchar(50), — single value
sandwich text[], — array
side text[][], — multi-dimensional array
dessert text ARRAY, — array
beverage text ARRAY[4] — array of 4 items
);

— insert array values into the table
INSERT INTO holiday_picnic VALUES
(‘Labor Day’,
‘{“roast beef”,”veggie”,”turkey”}’,
‘{{“potato salad”,”green salad”,”macaroni salad”},{“chips”,”crackers”,”NULL”}}’,
‘{“fruit cocktail”,”berry pie”,”ice cream”}’,
‘{“soda”,”juice”,”beer”,”water”}’
);

(Có một câu hỏi ở đây là liệu với PostgreSQL có tồn tại mảng không đối xứng 3×2 không?)

MySQL, MariaDB, và Firebird không có khả năng này. Để lưu trữ mảng các giá trị như thế này trong một cơ sở dữ liệu truyền thống, một bảng riêng với một dòng cho mỗi giá trị mảng có thể được tạo ra như một cách giải quyết.

Dữ liệu hình học

Dữ liệu hình học nhanh chóng trở thành một yêu cầu cốt lõi cho nhiều ứng dụng.  PostgreSQL từ lâu đã hỗ trợ một loạt kiểu dữ liệu hình học như points, lines, circles, và polygons. Kiểu dữ liệu PATH là một trong số đó. Một path bao gồm nhiều điểm trong một chuỗi và có thể là mở (điểm đầu và điểm cuối không nối với nhau) hoặc đóng (điểm đầu và điểm cuối nối với nhau). Hãy sử dụng một ví dụ đường mòn đi bộ đường dài như là một path. Trong trường hợp này, đường mòn đi bộ đường dài của tôi là một vòng lặp vì thế điểm bắt đầu và điểm kết thúc của tôi được nối với nhau, và vì vậy path của tôi là đóng. Cặp ngoặc đơn () xung quanh tập các tọa độ báo hiệu một path đóng trong khi một cặp ngoặc [ ] báo hiệu path mở.

— create a table for trails
CREATE TABLE trails (
trail_name varchar(250),
trail_path path
);

— insert a trail into the table
— where the path is defined by lat-long coordinates
INSERT INTO trails VALUES
(‘Dool Trail – Creeping Forest Trail Loop’,
‘((37.172,-122.22261666667),
(37.171616666667,-122.22385),
(37.1735,-122.2236),
(37.175416666667,-122.223),
(37.1758,-122.22378333333),
(37.179466666667,-122.22866666667),
(37.18395,-122.22675),
(37.180783333333,-122.22466666667),
(37.176116666667,-122.2222),
(37.1753,-122.22293333333),
(37.173116666667,-122.22281666667))’
);

Phần mở rộng PostGIS có sẵn cho PostgreSQL tăng các tính năng dữ liệu hình học có sẵn với việc thêm các kiểu không gian, các hàm, các thao tác và các chỉ mục. Nó là vị trí nhận biết và hỗ trợ cả dữ liệu raster và vector. Nó cũng cung cấp khả năng tương tác với một loạt mã nguồn mở của bên thứ 3 và các công cụ không gian địa lý độc quyền để làm việc, lập bản đồ và vẽ các dữ liệu.

Chú ý rằng MySQL 5.7.8 và từ MariaDB 5.3.3, phần mở rộng kiểu dữ liệu đã được thêm vào để hỗ trợ tiêu chuẩn OpenGIS cho thông tin địa lý. Phiên bản này của MySQL và các phiên bản tiếp theo của MariaDB cung cấp lưu trữ kiểu dữ liệu tương tự các kiểu dữ liệu hình học. Tuy nhiên, trong MySQL và MariaDB, các giá trị dữ liệu trước tiên phải chuyển đổi thành định dạng hình học sử dụng các lệnh đơn giản trước khi chèn vào các bảng. Firebird hiện tại không cung cấp các kiểu dữ liệu hình học.

Hỗ trợ JSON

Hỗ trợ JSON của PostgreSQL cho phép bạn ít sơ đồ (schema-less) trong một cơ sở dữ liệu SQL. Điều này có thể hữu ích khi cấu trúc dữ liệu yêu cầu phải có sự linh hoạt bởi vì nó vẫn thay đổi trong quá trình phát triển hoặc khi nó không biết số lượng, loại các trường dữ liệu mà đối tượng dữ liệu sẽ chứa.

CREATE TABLE justjson (id INTEGER, doc JSON);

INSERT INTO justjson VALUES (1,
‘{
“name”:”fred”,
“address”:{
“line1″:”52 The Elms”,
“line2″:”Elmstreet”,
“postcode”:”ES1 1ES”
}
}’
);

select doc->>’address’ FROM justjson;

select doc->’address’->>’postcode’ FROM justjson;

select doc#>>'{address,postcode}’ FROM justjson;

Cũng có sẵn kiểu JSONB – một dạng nhị phân của JSON nơi các khoảng trắng được loại bỏ, thứ tự đối tượng không được bảo đảm nhưng có ưu điểm là lưu trữ tối ưu, và chỉ giá trị cuối cùng cho các khóa trùng lặp được giữ lại. JSONB thường là định dạng được ưa thích khi nó yêu cầu ít khoảng trống cho đối tượng, có thể đánh chỉ mục và có thể xử lý nhanh hơn vì nó không đòi hỏi parsing lại. Để biết nhiều hơn, bạn hãy đọc bài viết: PostgreSQL có phải là cơ sở dữ liệu JSON tiếp theo của bạn?

Trong MySQL 5.7.8 và MariaDB 10.0.1 hỗ trợ sẵn cho các đối tượng JSON đã được giới thiệu ở trên. Trong khi có rất nhiều hàm và thao tác cho JSON có sẵn trong các cơ sở dữ liệu này, chúng lại không có khả năng đánh chỉ mục theo cách của JSONB trong PostgreSQL. Firebird chưa tham gia nhóm này và chỉ hỗ trợ đối tượng JSON như văn bản.

Tạo một kiểu mới

Và nếu danh sách các loại dữ liệu mở rộng có sẵn của PostgreSQL là không đủ, bạn có thể sử dụng lệnh CREATE TYPE để tạo ra các kiểu dữ liệu mới như hỗn hợp (composite), liệt kê (enumerated), khoảng (range) và base. Đây là một ví dụ của việc tạo và truy vấn một kiểu dữ liệu mới:

— create a new composite type called “wine”
CREATE TYPE wine AS (
wine_vineyard varchar(50),
wine_type varchar(50),
wine_year int
);

— create a table that uses the composite type “wine”
CREATE TABLE pairings (
menu_entree varchar(50),
wine_pairing wine
);

— insert data into the table using the ROW expression
INSERT INTO pairings VALUES
(‘Lobster Tail’,ROW(‘Stag”s Leap’,’Chardonnay’, 2012)),
(‘Elk Medallions’,ROW(‘Rombauer’,’Cabernet Sauvignon’,2012));

/*
query from the table using the table column name
(use parentheses followed by a period
then the name of the field from the composite type)
*/
SELECT (wine_pairing).wine_vineyard, (wine_pairing).wine_type
FROM pairings
WHERE menu_entree = ‘Elk Medallions’;

Bởi vì không phải là cơ sở dữ liệu quan hệ hướng đối tượng, MySQL, MariaDB và Firebird không cung cấp các chức năng mạnh mẽ này.

4. Kích thước dữ liệu

PostgreSQL có thể xử lý một lượng lớn dữ liệu. Các giới hạn về kích thước gửi được liệt kê dưới đây:

Giới hạn Giá trị
Kích thước tối đa của cơ sở dữ liệu Không giới hạn
Kích thước tối đa của table 32 TB
Kích thước tối đa của dòng (row) 1.6 TB
Kích thước tối đa của trường (field) 1 GB
Số hàng tối đa trên mỗi bảng Không giới hạn
Số cột tối đa trên mỗi bảng 250 – 1600 phụ thuộc vào kiểu cột
Số lượng index tối đa trên mỗi bảng Không giới hạn

Hãy so sánh, MySQL và MariaDB  được biết đến với giới hạn kích thước dòng là 65,535 byte. Firebird cũng chỉ có kích thước tối đa cho một dòng là 64KB. Thông thường kích thước dữ liệu được giới hạn bởi giới hạn kích thước file của hệ điều hành. Bởi vì PostgreSQL có thể lưu trữ bảng dữ liệu trong nhiều file nhỏ hơn, nên nó có thể khắc phục được hạn chế này – mặc dù, điều quan trọng cần lưu ý là quá nhiều file có thể tác động tiêu cực đến hiệu năng. MySQL và MariaDB cũng làm được, tuy nhiên chúng hỗ trợ nhiều cột trên một bảng (lên tới 4,096 phụ thuộc vào kiểu dữ liệu) và kích thước mỗi bảng lớn hơn PostgreSQL, nhưng rất hiếm các điều kiện mà bạn cần thực hiện vượt quá các giới hạn của PostgreSQL.

5. Toàn vẹn dữ liệu

PostgreSQL phấn đấu để phù hợp với chuẩn ANSI-SQL 2008, tuân thủ đầy đủ ACID (Atomicity, Consitency, Isolation và Durability), và cũng được biết đến các tham chiếu vững chắc của nó (rock-solid referential) và toàn vẹn giao dịch (transactional integrity). Các khóa chính, hạn chế và các tầng khóa ngoài, các ràng buộc duy nhất, các ràng buộc not null, kiểm tra các ràng buộc và các tính năng toàn vẹn dữ liệu khác để chắc chắn chỉ dữ liệu hợp lệ mới được lưu trữ.

MySQL và MariaDB đang làm nhiều hơn việc tuân thủ các chuẩn SQL với các cỗ máy lưu trữ InnoDB/XtraDB. Chúng cung cấp một tùy chọn STRICT xác định các cách kiểm tra sự hợp lệ của dữ liệu được sử dụng. Tuy nhiên, phụ thuộc vào phương thức bạn sử dụng, dữ liệu không hợp lệ và đôi khi âm thầm bị cắt ngắn (silently-truncated) có thể được chèn hoặc được tạo khi cập nhật. Hiện tại cũng không có cơ sở dữ liệu nào hỗ trợ kiểm tra các ràng buộc và cũng có một số khó khăn cho việc ràng buộc các khóa ngoài. Ngoài ra, toàn vẹn dữ liệu có thể phụ thuộc đáng kể và cỗ máy lưu trữ được lựa chọn. MySQL (và MariaDB fork) không giấu giếm rằng đã từ lâu họ cân bằng giữa tốc độ và hiệu suất hơn là tuân thủ tính toàn vẹn.

6. Đánh chỉ mục

PostgreSQL cung cấp khả năng đánh chỉ mục mà những cơ sở dữ liệu mã nguồn mở khác không có. PostgreSQL hỗ trợ các loại chỉ mục partial, expression, GiSTGIN và BRIN. Chúng ta hãy xem xét một số loại chỉ mục đặc biệt này.

Partial Indexes (Đánh chỉ mục từng phần)

Partial Indexes có thể được tạo ra khi bạn chỉ muốn đánh chỉ mục một tập hợp con của bảng, chẳng hạn như chỉ một số dòng nơi giá trị của cột phù hợp với một điều kiện cụ thể. Lợi thế của tính năng này giúp chỉ mục của bạn có kích thước hợp lý, với mục tiêu để cải thiện hiệu năng và giảm kích thước trên ổ đĩa. Một khía cạnh quan trọng của partial indexes là cột được đánh chỉ mục có thể khác so với các điều kiện được cung cấp. Ví dụ, bạn có thể muốn đánh chỉ mục chỉ các tài khoản của những khách hàng trả tiền thực, chứ không phải những tài khoản được tạo ra để kiểm thử nội bộ:

— create index of only paying customers
CREATE INDEX paying_accounts_idx ON accounts (account_id)
WHERE account_type <> ‘test’;

Điều quan trọng phải lưu ý rằng thỉnh thoảng trong MySQL thuật ngữ “partial indexes” được sử dụng để đề cập tới cắt giảm các giá trị được đánh chỉ mục thành một số byte nhất định, không giới hạn số lượng các dòng được đánh chỉ mục dựa trên điều kiện. Partial indexes như chúng ta đang mô tả ở đây không được hỗ trợ bởi MySQL.

Expression Indexes

Expression indexes có thể được tạo ra bằng cách sử dụng bất kỳ function nào để tính toán trước một cột trong chỉ mục. Các giá trị mới được đánh chỉ mục và được xem như các hằng số để truy vấn, đối lập với việc phải tính toán mỗi khi một truy vấn chạy. Một ví dụ là, nếu bạn có một trang web hit log thu thập số truy cập URL trong bất cứ định dạng nào mà chúng nhận được, bạn có thể muốn tạo ra một index của những URL dạng viết thường để chuẩn hóa dữ liệu (PostgreSQL có phân biệt chữ hoa chữ thường – io và Io được xem là các kết quả khác nhau):

— create index of lower-cased URLs
CREATE INDEX webhits_lower_urls_idx ON webhits (lower(url));

GIST và GIN (BRIN đang trong quá trình phát triển)

GiST (Generalized Search Tree) cho phép kết hợp B-tree, R-tree, và các kiểu đánh chỉ mục người dùng tự định nghĩa để tạo các chỉ mục tùy chỉnh với khả năng truy vấn tiên tiến. GiST đã được sử dụng trong PostGIS, và OpenFTS (một cỗ máy tìm kiếm full text mã nguồn mở).

PostgreSQL cũng hỗ trợ SP-GiST cho phép tạo phân vùng chỉ mục tìm kiếm để tăng tốc độ truy xuất.

GIN (Generalized Inverted Index) cho phép đánh chỉ mục các kiểu dữ liệu kết hợp. Các kiểu dữ liệu kết hợp cho phép bạn kết hợp các kiểu dữ liệu khác nhau theo nhiều cách để tạo ra một cái gì đó hoàn toàn tùy chỉnh.

BRIN cho phép chia các bảng lớn thành các khoảng dựa trên cột đã được đánh chỉ muc. Điều này có nghĩa là các kế hoạch truy vấn có thể quét trong khoảng đã được chỉ định bởi truy vấn. Vì thế, nhờ các khoảng chỉ mục, số lượng kích thước đĩa cần thiết cho các chỉ mục sẽ nhỏ hơn một chỉ mục chuẩn B-Tree.

Để tạo các chỉ mục GIST và GIN, ta dùng cú pháp là: CREATE INDEX… ON… USING GIST|GIN…

So sánh

Các cơ sở dữ liệu SQL khác mà chúng ta thấy đang thu hẹp khoảng cách khi nói đến expression indexes (Với MySQL, MariaDB và Firebird, generated columns có thể sử dụng như một expression index).

Tuy nhiên, không có cơ sở dữ liệu nào hỗ trợ đánh chỉ mục partial, GiST hoặc GIN. Vì thế, như chúng ta đã đề cập trong phần 1, các kiểu dữ liệu JSON tự nhiên không thể đánh chỉ mục trong các cơ sở dữ liệu này.

Và bạn đừng quên đọc bài viết: Kiểm tra chỉ mục đơn giản với PostgreSQL (Simple Index Checking with PostgreSQL) của tác giả Matt Barr khi bạn có các chỉ mục và muốn phân tích hiệu suất của chúng.

7. Các tính năng bảng ảo

Các bảng ảo là cần thiết trong nhiều truy vấn. Tất cả các cơ sở dữ liệu SQL chúng tôi đã so sánh cung cấp một số chức năng bảng ảo. PostgreSQL còn cung cấp cho bạn nhiều hơn thế.

CTEs và đệ quy

PostgreSQL hỗ trợ Common Table Expressions (CTEs) sử dụng mệnh đề WITH. Chúng tôi đã minh họa ví dụ này trong bài viết: PostgreSQL – Series, Random and With. CTEs cho phép bạn tạo các bảng ảo nối tiếp trong các truy vấn của mình, thể hiện hợp lý thứ tự các thao tác để có thể dễ đọc và đảm bảo chất lượng hơn trong việc tạo các bảng ảo sử dụng các truy vấn con ở nơi khác trong truy vấn. CTEs trong PostgreSQL cũng có thể sử dụng đệ quy. Khả năng tiện dụng này cho phép bạn duyệt qua một hệ thống phân cấp, với các truy vấn self-referring (gọi đến chính nó) liên tục cho đến khi không còn level dữ liệu nào có thể được trả về nữa. Dưới đây là một ví dụ của một CTE đệ quy xác định các level, chủ đề, và các quan hệ cha con trong phân loại chủ đề.

— query with recursive
CTE WITH RECURSIVE topic_taxonomy_recursive
(level, parent_topic_name, topic_name)
AS (
SELECT 1, tt.parent_topic_name, tt.topic_name
FROM topic_taxonomy tt
WHERE tt.parent_topic_name = ‘All Topics’
UNION ALL
SELECT ttr.level + 1, tt.parent_topic_name, tt.topic_name
FROM topic_taxonomy_recursive ttr, topic_taxonomy tt
WHERE ttr.topic_name = tt.parent_topic_name
) SELECT level, parent_topic_name, topic_name
FROM topic_taxonomy_recursive;

MySQL và MariaDB không sử dụng mệnh đề WITH và vì thế không chính thức hỗ trợ CTEs. Trong khi bạn có thể tạo các bảng có nguồn gốc sử dụng các truy vấn phụ (subqueries) trong các cơ sở dữ liệu này, chúng không cho phép đệ quy. Ngoài ra, mặc dù truy vấn tối ưu trong MySQL đã được cải thiện từ phiên bản 5.6, việc lồng các truy vấn phụ được biết đến là khá phiền hà và có thể ảnh hưởng đáng kể đến hiệu suất trong cơ sở dữ liệu này. Trái lại, Firebird hỗ trợ CTEs sử dụng WITH và cũng cung cấp khả năng đệ quy.

Materialized views

Materialized views là tính năng bảng ảo tiện lợi khác đã được hỗ trợ bởi PostgreSQL. Materialized views giống như các view thông thường trong việc trình diễn tập kết quả của một truy vấn mà bạn muốn sử dụng thường xuyên, ngoại trừ tập kết quả thực sự được lưu trữ trên ổ đĩa giống như các bảng thông thường. Materialized views cũng có thể được đánh chỉ mục, không giống các views thông thường được tạo ra mỗi lần chúng được gọi, materialized views là các ảnh chụp tại một thời điểm. Chúng không được làm mới (refreshed) ngoại trừ được chỉ định. Điều này có thể tăng đáng kể tốc độ với các truy vấn chạy sử dụng materialized views. Thay vì sử dụng các views thông thường hoặc phải làm các join phức tạp trên các bảng hoặc thực hiện các hàm tập hợp (aggregation funcitons) trong truy vấn, sử dụng materialized vies với các dữ liệu cần thiết sẵn sàng và chờ đợi trên ổ đĩa để tăng hiệu quả. Khi bạn sẵn sàng cập nhật dữ liệu trong materialized view, nó có thể làm mới sử dụng lệnh REFRESH. Dưới đây là một ví dụ của một materialized view tạo ra dữ liệu doanh thu tổng hợp:

— create an aggregated revenue result as a materialized view
CREATE MATERIALIZED VIEW aggregatedMonthlyRevenue
(year, month, total_revenue)
AS (
SELECT date_part(‘year’, date) AS year,
date_part(‘month’, date) AS month,
SUM(revenue) AS total_revenue
FROM revenue
WHERE date >= ‘2014-01-01’
GROUP BY date_part(‘year’, date), date_part(‘month’, date)
ORDER BY date_part(‘year’, date), date_part(‘month’, date)
);

— refresh the materialized view as needed
REFRESH MATERIALIZED VIEW aggregatedMonthlyRevenue;

Firebird, MySQL và MariaDB không hỗ trợ materialized views, mặc dù có thể giải quyết bằng cách tạo một bảng thông thường sử dụng stored procedure hoặc trigger để cập nhật các yêu cầu.

8. Các khả năng truy vấn

Kết hợp các truy vấn

PostgreSQL cung cấp các mệnh đề UNION, INTERSECTEXCEPT để tương tác giữa các câu lệnh SELECTUNION sẽ nối các kết quả của câu lệnh SELECT thứ 2 tới kết quả của câu lệnh đầu tiên. INTERSECT chỉ trả lại các hàng phù hợp với tất cả các câu lệnh SELECTEXCEPT chỉ trả lại các dòng từ câu lệnh SELECT đầu tiên mà không phù hợp với các hàng từ câu lệnh SELECT thứ 2. Hãy xem một ví dụ sử dụng EXCEPT nơi chúng ta trả lại thông tin liên hệ của khách hàng ngoại trừ trường hợp khách hàng đã nhận và trả lời một email trong tuần trước.

/*
query to get customer info
where the customer has not been contacted
and responded in the past week
*/
SELECT c.lastName, c.firstName, c.email
FROM customers c
EXCEPT
SELECT e.lastName, e.firstName, e.email
FROM email_log e
WHERE e.email_date > current_date – interval ‘7 days’
AND e.email_action_date > current_date – interval ‘7 days’
AND e.email_action_type = ‘response’;

Trong khi MySQL, MariaDB, và Firebird đều hỗ trợ UNION, không cơ sở dữ liệu nào hỗ trợ INTERSECT hoặc EXCEPT. Tuy nhiên, vẫn có thể thu được kết quả tương tự như thế này bằng các truy vấn phức tạp hơn.

Window functions

Window functions thực hiện các hàm tổng hợp trên một vài dòng của tập kết quả (cung cấp một “window” trong tập con). Chúng chủ yếu cho phép bạn lặp qua các dòng trong phân vùng liên quan tới dòng hiện tại để thực hiện các hàm. Các hàm phổ biến gồm có ROW_NUMBER(), RANK(), DENSE_RANK()PERCENT_RANK(). Từ khóa OVER không bắt buộc sử dụng với PARTITION BYORDER BY, chỉ ra rằng window function đang được sử dụng. Chú ý rằng mệnh đề WINDOW không bắt buộc trong các truy vấn với Window functions, nhưng nó cho phép bạn tạo ra tên các window để giúp giữ cho mọi thứ đơn giản.

Firebird, MySQL và MariaDB hiện tại không hỗ trợ window functions, mặc dù window functions đã được tuyên bố cách đây vài năm trong kế hoạch của Firebird.

Lateral subqueries

Từ khóa LATERAL có thể áp dụng cho các truy vấn con trong mệnh đề FROM để cho phép bạn tham chiếu chéo giữa truy vấn con và các bảng khác hoặc các bảng ảo đã được tạo trước đó. Các truy vấn có thể viết đơn giản hơn theo cách này.

Ngoài ra, cách nó hoạt động là mỗi hàng được đánh giá đối với các bảng tham chiếu chéo, trong đó có thể cải thiện tốc độ trong quá trình xử lý truy vấn. Đây là một ví dụ, chúng ta muốn một danh sánh các sinh viên và để biết liệu họ đã đọc chủ đề công nghệ theo định hướng thời gian gần đây hay chưa:

— query using a lateral subquery
SELECT s.firstName, s.LastName, x.topic_name
FROM students s
JOIN content_log c ON c.student_id = s.id
LEFT OUTER JOIN LATERAL (

SELECT t.topic_name
FROM content_topics t
WHERE t.parent_topic_name = ‘Technology’
AND t.id = c.topic_id
AND c.date > current_date – interval ’30 days’
) x ON true;

MySQL, Firebird và MariaDB hiện tại không hỗ trợ lateral subqueries, nhưng vẫn giải quyết được bằng các truy vấn phức tạp hơn.

Các hàm và hơn nữa

PostgreSQL hỗ trợ 4 loại hàm người dùng tự định nghĩa: ngôn ngữ truy vấn, ngôn ngữ thủ tục, ngôn ngữ C và nội bộ. Mỗi loại có thể lấy và trả lại đồng thời dựa trên các kiểu hỗn hợp. Chú ý trong PostgreSQL lệnh CREATE FUNCTION được sử dụng để tạo stored procedures cũng như các hàm.

Hãy xem một ví dụ tạo một hàm trả lại một kiểu dữ liệu hỗn hợp:

— create a new composite type called “datetext”
CREATE TYPE datetext AS (
date date,
date_as_text text
);

/*
create a function that takes the date
then returns the date and the datetext
*/
CREATE FUNCTION show_date_as_text(date)
RETURNS datetext — this is our composite type
AS
$$
SELECT $1, to_char($1, ‘FMMonth FMDD, YYYY’)
$$
LANGUAGE SQL;

— query using the function
SELECT show_date_as_text(‘2015-01-01’);

— Returns: (2015-01-01,”January 1, 2015″)

Dưới đây là một chức năng tùy chỉnh thiết thực cho việc tìm kiếm giá trị trung bình trong một loạt dữ liệu số:

— create a function that finds the median in a numeric data series
CREATE FUNCTION median(numeric[])
RETURNS numeric
AS
$$
SELECT AVG(x.result)
FROM (
SELECT result,
ROW_NUMBER() OVER (ORDER BY val) as ra,
ROW_NUMBER() OVER (ORDER BY val DESC) as rd
FROM unnest($1) result — notice the use of array “unnest”
) AS x
WHERE x.ra BETWEEN x.rd – 1 AND x.rd + 1;
$$
LANGUAGE SQL;

— query using the function
SELECT median(ARRAY[1,2,3,4,5,6,7]);

— Returns: 4

Trong các cơ sở dữ liệu SQL mã nguồn mở chúng ta đang so sánh cũng cho phép bạn tạo các hàm, stored procedures và triggers của riêng mình, chúng không có nhiều kiểu dữ liệu và các tùy chọn tùy chỉnh như PostgreSQL cung cấp. Ngoài ra, trong PostgreSQL bạn có thể tạo ra các thao tác của riêng mình. Các cơ sở dữ liệu so sánh khác không hỗ trợ các thao tác người dùng tự định nghĩa.

Các khả năng tùy chỉnh của PostgreSQL là không có đối thủ khi so sánh với MySQL, MariaDB và Firebird.

Link bài viết gốc:
https://www.compose.com/articles/what-postgresql-has-over-other-open-source-sql-databases/
https://www.compose.com/articles/what-postgresql-has-over-other-open-source-sql-databases-part-ii/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s