PostgreSQL - Kiểu dữ liệu
Trong chương này, chúng ta sẽ thảo luận về các kiểu dữ liệu được sử dụng trong PostgreSQL. Trong khi tạo bảng, đối với mỗi cột, bạn chỉ định kiểu dữ liệu, tức là loại dữ liệu bạn muốn lưu trữ trong các trường của bảng.
Điều này mang lại một số lợi ích -
Consistency - Các phép toán so với các cột cùng kiểu dữ liệu cho kết quả nhất quán và thường là nhanh nhất.
Validation - Việc sử dụng đúng kiểu dữ liệu có nghĩa là xác nhận định dạng của dữ liệu và từ chối dữ liệu ngoài phạm vi của kiểu dữ liệu.
Compactness - Là một cột có thể lưu trữ một loại giá trị duy nhất, nó được lưu trữ một cách nhỏ gọn.
Performance- Sử dụng hợp lý các kiểu dữ liệu mang lại hiệu quả lưu trữ dữ liệu cao nhất. Các giá trị được lưu trữ có thể được xử lý nhanh chóng, giúp nâng cao hiệu suất.
PostgreSQL hỗ trợ nhiều loại Dữ liệu. Bên cạnh đó, người dùng có thể tạo kiểu dữ liệu tùy chỉnh của riêng mình bằng lệnh CREATE TYPE SQL. Có nhiều danh mục kiểu dữ liệu khác nhau trong PostgreSQL. Chúng được thảo luận dưới đây.
Các loại số
Các kiểu số bao gồm các số nguyên hai byte, bốn byte và tám byte, số dấu phẩy động bốn byte và tám byte và số thập phân có độ chính xác có thể chọn. Bảng sau liệt kê các loại có sẵn.
| Tên | Kích thước lưu trữ | Sự miêu tả | Phạm vi |
|---|---|---|---|
| lửa nhỏ | 2 byte | số nguyên phạm vi nhỏ | -32768 đến +32767 |
| số nguyên | 4 byte | lựa chọn điển hình cho số nguyên | -2147483648 đến +2147483647 |
| bigint | 8 byte | số nguyên phạm vi lớn | -9223372036854775808 đến 9223372036854775807 |
| thập phân | Biến đổi | độ chính xác do người dùng chỉ định, chính xác | lên đến 131072 chữ số trước dấu thập phân; lên đến 16383 chữ số sau dấu thập phân |
| số | Biến đổi | độ chính xác do người dùng chỉ định, chính xác | lên đến 131072 chữ số trước dấu thập phân; lên đến 16383 chữ số sau dấu thập phân |
| thực tế | 4 byte | độ chính xác thay đổi, không chính xác | 6 chữ số thập phân chính xác |
| chính xác gấp đôi | 8 byte | độ chính xác thay đổi, không chính xác | 15 chữ số thập phân chính xác |
| tài liệu nhỏ | 2 byte | số nguyên tự động cộng nhỏ | 1 đến 32767 |
| nối tiếp | 4 byte | tự động cộng gộp số nguyên | 1 đến 2147483647 |
| quan trọng | 8 byte | số nguyên tự động cộng gộp lớn | 1 đến 9223372036854775807 |
Các loại tiền tệ
Các tiền kiểu lưu trữ một lượng tiền tệ với một độ chính xác phân đoạn cố định. Giá trị của các kiểu dữ liệu số, int và bigint có thể được chuyển thành tiền . Không nên sử dụng số Dấu phẩy động để xử lý tiền do có thể xảy ra lỗi làm tròn.
| Tên | Kích thước lưu trữ | Sự miêu tả | Phạm vi |
|---|---|---|---|
| tiền bạc | 8 byte | lượng ngoại tệ | -92233720368547758,08 đến +92233720368547758,07 |
Các loại ký tự
Bảng dưới đây liệt kê các kiểu ký tự có mục đích chung trong PostgreSQL.
| S. Không. | Tên & Mô tả |
|---|---|
| 1 | character varying(n), varchar(n) chiều dài thay đổi có giới hạn |
| 2 | character(n), char(n) chiều dài cố định, đệm trống |
| 3 | text chiều dài biến đổi không giới hạn |
Kiểu dữ liệu nhị phân
Kiểu dữ liệu bytea cho phép lưu trữ các chuỗi nhị phân như trong bảng dưới đây.
| Tên | Kích thước lưu trữ | Sự miêu tả |
|---|---|---|
| bytea | 1 hoặc 4 byte cộng với chuỗi nhị phân thực tế | chuỗi nhị phân có độ dài thay đổi |
Các loại ngày / giờ
PostgreSQL hỗ trợ một tập hợp đầy đủ các kiểu ngày và giờ của SQL, như được hiển thị trong bảng bên dưới. Ngày được tính theo lịch Gregory. Ở đây, tất cả các loại đều có độ phân giải1 microsecond / 14 digits ngoại trừ date loại, có độ phân giải là day.
| Tên | Kích thước lưu trữ | Sự miêu tả | Giá trị thấp | Giá trị cao |
|---|---|---|---|---|
| dấu thời gian [(p)] [không có múi giờ] | 8 byte | cả ngày và giờ (không có múi giờ) | 4713 trước công nguyên | 294276 SCN |
| TIMESTAMPTZ | 8 byte | cả ngày và giờ, với múi giờ | 4713 trước công nguyên | 294276 SCN |
| ngày | 4 byte | ngày (không có thời gian trong ngày) | 4713 trước công nguyên | 5874897 sau Công nguyên |
| time [(p)] [không có múi giờ] | 8 byte | thời gian trong ngày (không có ngày) | 00:00:00 | 24:00:00 |
| time [(p)] với múi giờ | 12 byte | chỉ thời gian trong ngày, với múi giờ | 00: 00: 00 + 1459 | 24: 00: 00-1459 |
| khoảng thời gian [các trường] [(p)] | 12 byte | Khoảng thời gian | -178000000 năm | 178000000 năm |
Loại Boolean
PostgreSQL cung cấp kiểu SQL Boolean tiêu chuẩn. Kiểu dữ liệu Boolean có thể có trạng thái true , false và trạng thái thứ ba, không xác định , được biểu diễn bằng giá trị rỗng trong SQL.
| Tên | Kích thước lưu trữ | Sự miêu tả |
|---|---|---|
| boolean | 1 byte | trạng thái đúng hay sai |
Loại được liệt kê
Kiểu liệt kê (enum) là kiểu dữ liệu bao gồm một tập giá trị tĩnh, có thứ tự. Chúng tương đương với các kiểu enum được hỗ trợ trong một số ngôn ngữ lập trình.
Không giống như các kiểu khác, Kiểu liệt kê cần được tạo bằng lệnh CREATE TYPE. Kiểu này được sử dụng để lưu trữ một bộ giá trị tĩnh, có thứ tự. Ví dụ hướng la bàn, tức là BẮC, NAM, ĐÔNG và TÂY hoặc các ngày trong tuần như hình dưới đây -
CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');Enumerated, sau khi được tạo, có thể được sử dụng giống như bất kỳ loại nào khác.
Loại hình học
Các kiểu dữ liệu hình học đại diện cho các đối tượng không gian hai chiều. Loại cơ bản nhất, điểm, tạo cơ sở cho tất cả các loại khác.
| Tên | Kích thước lưu trữ | Đại diện | Sự miêu tả |
|---|---|---|---|
| điểm | 16 byte | Chỉ trên một mặt phẳng | (x, y) |
| hàng | 32 byte | Dòng vô hạn (không được triển khai đầy đủ) | ((x1, y1), (x2, y2)) |
| lseg | 32 byte | Đoạn thẳng hữu hạn | ((x1, y1), (x2, y2)) |
| cái hộp | 32 byte | Hộp hình chữ nhật | ((x1, y1), (x2, y2)) |
| con đường | 16 + 16n byte | Đường dẫn đóng (tương tự như đa giác) | ((x1, y1), ...) |
| con đường | 16 + 16n byte | Con đường mở | [(x1, y1), ...] |
| đa giác | 40 + 16n | Đa giác (tương tự như đường dẫn đóng) | ((x1, y1), ...) |
| vòng tròn | 24 byte | Vòng tròn | <(x, y), r> (tâm điểm và bán kính) |
Loại địa chỉ mạng
PostgreSQL cung cấp các kiểu dữ liệu để lưu trữ địa chỉ IPv4, IPv6 và MAC. Tốt hơn là sử dụng các kiểu này thay vì các kiểu văn bản thuần túy để lưu trữ địa chỉ mạng, vì những kiểu này cung cấp tính năng kiểm tra lỗi đầu vào và các toán tử và chức năng chuyên biệt.
| Tên | Kích thước lưu trữ | Sự miêu tả |
|---|---|---|
| cidr | 7 hoặc 19 byte | Mạng IPv4 và IPv6 |
| inet | 7 hoặc 19 byte | Máy chủ và mạng IPv4 và IPv6 |
| macaddr | 6 byte | Địa chỉ MAC |
Loại chuỗi bit
Loại chuỗi bit được sử dụng để lưu trữ mặt nạ bit. Chúng là 0 hoặc 1. Có hai kiểu bit SQL:bit(n) và bit varying(n), với n là số nguyên dương.
Loại tìm kiếm văn bản
Loại này hỗ trợ tìm kiếm toàn văn bản, là hoạt động tìm kiếm thông qua một bộ sưu tập các tài liệu bằng ngôn ngữ tự nhiên để tìm những tài liệu phù hợp nhất với một truy vấn. Có hai Loại Dữ liệu cho việc này -
| S. Không. | Tên & Mô tả |
|---|---|
| 1 | tsvector Đây là danh sách được sắp xếp gồm các từ riêng biệt đã được chuẩn hóa để hợp nhất các biến thể khác nhau của cùng một từ, được gọi là "lexemes". |
| 2 | tsquery Điều này lưu trữ các từ vựng cần tìm và kết hợp chúng với nhau để tôn vinh các toán tử Boolean & (AND), | (Hoặc và ! (KHÔNG PHẢI). Dấu ngoặc đơn có thể được sử dụng để thực thi nhóm các toán tử. |
Loại UUID
UUID (Số nhận dạng duy nhất phổ biến) được viết dưới dạng một chuỗi các chữ số thập lục phân viết thường, trong một số nhóm được phân tách bằng dấu gạch ngang, cụ thể là một nhóm tám chữ số, tiếp theo là ba nhóm bốn chữ số, tiếp theo là một nhóm 12 chữ số, cho tổng số 32 chữ số đại diện cho 128 bit.
Ví dụ về UUID là - 550e8400-e29b-41d4-a716-446655440000
Loại XML
Kiểu dữ liệu XML có thể được sử dụng để lưu trữ dữ liệu XML. Để lưu trữ dữ liệu XML, trước tiên bạn phải tạo các giá trị XML bằng cách sử dụng hàm xmlparse như sau:
XMLPARSE (DOCUMENT '<?xml version="1.0"?>
<tutorial>
<title>PostgreSQL Tutorial </title>
<topics>...</topics>
</tutorial>')
XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')Loại JSON
Kiểu dữ liệu json có thể được sử dụng để lưu trữ dữ liệu JSON (JavaScript Object Notation). Dữ liệu như vậy cũng có thể được lưu trữ dưới dạng văn bản , nhưng kiểu dữ liệu json có lợi thế là kiểm tra rằng mỗi giá trị được lưu trữ là một giá trị JSON hợp lệ. Ngoài ra còn có các chức năng hỗ trợ liên quan có sẵn, có thể được sử dụng trực tiếp để xử lý kiểu dữ liệu JSON như sau.
| Thí dụ | Kết quả mẫu |
|---|---|
| array_to_json ('{{1,5}, {99,100}}' :: int []) | [[1,5], [99,100]] |
| row_to_json (row (1, 'foo')) | {"f1": 1, "f2": "foo"} |
Loại mảng
PostgreSQL cho cơ hội xác định một cột của bảng là một mảng đa chiều có độ dài thay đổi. Có thể tạo các mảng thuộc bất kỳ kiểu cơ sở, kiểu enum hoặc kiểu kết hợp nào được tích hợp sẵn hoặc do người dùng xác định.
Khai báo mảng
Kiểu mảng có thể được khai báo là
CREATE TABLE monthly_savings (
name text,
saving_per_quarter integer[],
scheme text[][]
);hoặc bằng cách sử dụng từ khóa "ARRAY" làm
CREATE TABLE monthly_savings (
name text,
saving_per_quarter integer ARRAY[4],
scheme text[][]
);Chèn giá trị
Giá trị mảng có thể được chèn dưới dạng hằng số chữ, bao quanh các giá trị phần tử trong dấu ngoặc nhọn và phân tách chúng bằng dấu phẩy. Một ví dụ được hiển thị bên dưới -
INSERT INTO monthly_savings
VALUES (‘Manisha’,
‘{20000, 14600, 23500, 13250}’,
‘{{“FD”, “MF”}, {“FD”, “Property”}}’);Truy cập Mảng
Một ví dụ để truy cập Mảng được hiển thị bên dưới. Lệnh dưới đây sẽ chọn những người có số tiền tiết kiệm trong quý II nhiều hơn quý IV.
SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];Sửa đổi mảng
Một ví dụ về sửa đổi mảng như được hiển thị bên dưới.
UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha';hoặc sử dụng cú pháp biểu thức ARRAY -
UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';Tìm kiếm Mảng
Ví dụ về tìm kiếm mảng như hình dưới đây.
SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 10000;Nếu kích thước của mảng được biết, có thể sử dụng phương pháp tìm kiếm ở trên. Ngoài ra, ví dụ sau cho thấy cách tìm kiếm khi không biết kích thước.
SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);Các loại hỗn hợp
Kiểu này đại diện cho danh sách các tên trường và kiểu dữ liệu của chúng, tức là cấu trúc của một hàng hoặc bản ghi của bảng.
Khai báo các loại kết hợp
Ví dụ sau đây cho thấy cách khai báo một kiểu kết hợp
CREATE TYPE inventory_item AS (
name text,
supplier_id integer,
price numeric
);Kiểu dữ liệu này có thể được sử dụng trong các bảng tạo như bên dưới:
CREATE TABLE on_hand (
item inventory_item,
count integer
);Đầu vào giá trị tổng hợp
Giá trị tổng hợp có thể được chèn dưới dạng hằng số chữ, bao quanh các giá trị trường trong dấu ngoặc đơn và phân tách chúng bằng dấu phẩy. Một ví dụ được hiển thị bên dưới -
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);Điều này hợp lệ cho kho_item được xác định ở trên. Từ khóa ROW thực sự là tùy chọn miễn là bạn có nhiều trường trong biểu thức.
Truy cập các loại hỗn hợp
Để truy cập một trường của cột tổng hợp, hãy sử dụng một dấu chấm theo sau tên trường, giống như chọn một trường từ tên bảng. Ví dụ: để chọn một số trường con từ bảng ví dụ on_hand của chúng tôi, truy vấn sẽ như được hiển thị bên dưới:
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;Bạn thậm chí có thể sử dụng tên bảng (ví dụ: trong một truy vấn nhiều bảng), như thế này -
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;Các loại phạm vi
Kiểu phạm vi đại diện cho kiểu dữ liệu sử dụng một phạm vi dữ liệu. Loại phạm vi có thể là phạm vi rời rạc (ví dụ: tất cả các giá trị nguyên từ 1 đến 10) hoặc phạm vi liên tục (ví dụ: bất kỳ thời điểm nào trong khoảng thời gian từ 10:00 sáng đến 11:00 sáng).
Các loại phạm vi tích hợp sẵn có bao gồm các phạm vi sau:
int4range - Phạm vi số nguyên
int8range - Phạm vi của bigint
numrange - Phạm vi số
tsrange - Phạm vi dấu thời gian không có múi giờ
tstzrange - Phạm vi dấu thời gian với múi giờ
daterange - Phạm vi ngày
Các loại phạm vi tùy chỉnh có thể được tạo để cung cấp các loại phạm vi mới, chẳng hạn như phạm vi địa chỉ IP sử dụng loại inet làm cơ sở hoặc phạm vi float sử dụng kiểu dữ liệu float làm cơ sở.
Loại phạm vi hỗ trợ các ranh giới phạm vi bao gồm và riêng biệt bằng cách sử dụng các ký tự [] và (), tương ứng. Ví dụ '[4,9)' đại diện cho tất cả các số nguyên bắt đầu từ và bao gồm 4 đến nhưng không bao gồm 9.
Các loại mã định danh đối tượng
Mã định danh đối tượng (OID) được PostgreSQL sử dụng nội bộ làm khóa chính cho các bảng hệ thống khác nhau. Nếu WITH OIDS được chỉ định hoặc biến cấu hình default_with_oids được bật thì chỉ khi đó, trong những trường hợp như vậy, OID mới được thêm vào các bảng do người dùng tạo. Bảng sau đây liệt kê một số loại bí danh. Các loại bí danh OID không có hoạt động của riêng chúng ngoại trừ các quy trình đầu vào và đầu ra chuyên biệt.
| Tên | Người giới thiệu | Sự miêu tả | Ví dụ về giá trị |
|---|---|---|---|
| oid | bất kì | định danh đối tượng số | 564182 |
| regproc | pg_proc | Tên chức năng | Tổng |
| sự bắt buộc | pg_proc | hàm với các loại đối số | sum (int4) |
| người đăng ký | pg_operator | Tên người vận hành | + |
| người điều hành | pg_operator | toán tử với các loại đối số | * (số nguyên, số nguyên) hoặc - (KHÔNG, số nguyên) |
| xếp loại | pg_class | tên quan hệ | pg_type |
| kiểu dáng lại | pg_type | tên kiểu dữ liệu | số nguyên |
| regconfig | pg_ts_config | cấu hình tìm kiếm văn bản | Tiếng Anh |
| thoái vị | pg_ts_dict | từ điển tìm kiếm văn bản | đơn giản |
Các loại giả
Hệ thống kiểu PostgreSQL chứa một số mục nhập có mục đích đặc biệt được gọi chung là kiểu giả. Kiểu giả không thể được sử dụng làm kiểu dữ liệu cột, nhưng nó có thể được sử dụng để khai báo đối số của hàm hoặc kiểu kết quả.
Bảng dưới đây liệt kê các kiểu giả hiện có.
| S. Không. | Tên & Mô tả |
|---|---|
| 1 | any Cho biết một hàm chấp nhận bất kỳ kiểu dữ liệu đầu vào nào. |
| 2 | anyelement Cho biết rằng một hàm chấp nhận bất kỳ kiểu dữ liệu nào. |
| 3 | anyarray Chỉ ra rằng một hàm chấp nhận bất kỳ kiểu dữ liệu mảng nào. |
| 4 | anynonarray Chỉ ra rằng một hàm chấp nhận bất kỳ kiểu dữ liệu không phải là mảng nào. |
| 5 | anyenum Chỉ ra rằng một hàm chấp nhận bất kỳ kiểu dữ liệu enum nào. |
| 6 | anyrange Cho biết rằng một hàm chấp nhận bất kỳ kiểu dữ liệu phạm vi nào. |
| 7 | cstring Chỉ ra rằng một hàm chấp nhận hoặc trả về một chuỗi C kết thúc bằng rỗng. |
| số 8 | internal Cho biết một hàm chấp nhận hoặc trả về kiểu dữ liệu nội bộ của máy chủ. |
| 9 | language_handler Một trình xử lý cuộc gọi ngôn ngữ thủ tục được khai báo để trả về language_handler. |
| 10 | fdw_handler Một trình xử lý trình bao bọc dữ liệu nước ngoài được khai báo để trả về fdw_handler. |
| 11 | record Xác định một hàm trả về loại hàng không xác định. |
| 12 | trigger Một hàm kích hoạt được khai báo để trả về kích hoạt. |
| 13 | void Cho biết rằng một hàm không trả về giá trị nào. |