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)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.