PostgreSQL - ชนิดข้อมูล

ในบทนี้เราจะพูดถึงประเภทข้อมูลที่ใช้ใน PostgreSQL ในขณะที่สร้างตารางคุณต้องระบุชนิดข้อมูลสำหรับแต่ละคอลัมน์นั่นคือชนิดของข้อมูลที่คุณต้องการจัดเก็บในฟิลด์ตาราง

สิ่งนี้ทำให้เกิดประโยชน์หลายประการ -

• Consistency - การดำเนินการกับคอลัมน์ที่มีข้อมูลประเภทเดียวกันให้ผลลัพธ์ที่สอดคล้องกันและโดยปกติจะเร็วที่สุด

• Validation - การใช้ประเภทข้อมูลอย่างเหมาะสมหมายถึงการตรวจสอบความถูกต้องของข้อมูลและการปฏิเสธข้อมูลนอกขอบเขตของประเภทข้อมูล

• Compactness - เนื่องจากคอลัมน์สามารถจัดเก็บค่าประเภทเดียวได้จึงถูกจัดเก็บอย่างกะทัดรัด

• Performance- การใช้ประเภทข้อมูลอย่างเหมาะสมทำให้การจัดเก็บข้อมูลมีประสิทธิภาพสูงสุด ค่าที่จัดเก็บสามารถประมวลผลได้อย่างรวดเร็วซึ่งช่วยเพิ่มประสิทธิภาพ

PostgreSQL รองรับประเภทข้อมูลที่หลากหลาย นอกจากนี้ผู้ใช้สามารถสร้างประเภทข้อมูลที่กำหนดเองได้โดยใช้คำสั่งCREATE TYPE SQL ประเภทข้อมูลใน PostgreSQL มีหลายประเภท พวกเขาจะกล่าวถึงด้านล่าง

ประเภทตัวเลข

ประเภทตัวเลขประกอบด้วยจำนวนเต็มสองไบต์สี่ไบต์และแปดไบต์ตัวเลขทศนิยมสี่ไบต์และแปดไบต์และทศนิยมที่มีความแม่นยำที่เลือกได้ ตารางต่อไปนี้แสดงประเภทที่ใช้ได้

ชื่อ	ขนาดการจัดเก็บ	คำอธิบาย	พิสัย
Smallint	2 ไบต์	จำนวนเต็มช่วงเล็ก	-32768 ถึง +32767
จำนวนเต็ม	4 ไบต์	ทางเลือกทั่วไปสำหรับจำนวนเต็ม	-2147483648 ถึง +2147483647
bigint	8 ไบต์	จำนวนเต็มช่วงใหญ่	-9223372036854775808 ถึง 9223372036854775807
ทศนิยม	ตัวแปร	ความแม่นยำที่ผู้ใช้ระบุแน่นอน	มากถึง 131072 หลักก่อนจุดทศนิยม มากถึง 16383 หลักหลังจุดทศนิยม
ตัวเลข	ตัวแปร	ความแม่นยำที่ผู้ใช้ระบุแน่นอน	มากถึง 131072 หลักก่อนจุดทศนิยม มากถึง 16383 หลักหลังจุดทศนิยม
จริง	4 ไบต์	ความแม่นยำตัวแปรไม่แน่นอน	ความแม่นยำของทศนิยม 6 หลัก
ความแม่นยำสองเท่า	8 ไบต์	ความแม่นยำตัวแปรไม่แน่นอน	ความแม่นยำของทศนิยม 15 หลัก
smallserial	2 ไบต์	จำนวนเต็มอัตโนมัติขนาดเล็ก	1 ถึง 32767
อนุกรม	4 ไบต์	การสร้างจำนวนเต็มอัตโนมัติ	1 ถึง 2147483647
bigserial	8 ไบต์	จำนวนเต็มอัตโนมัติขนาดใหญ่	1 ถึง 9223372036854775807

ประเภทการเงิน

เงินประเภทเก็บสกุลเงินที่มีความแม่นยำเศษส่วนคงที่ ค่าของประเภทข้อมูลตัวเลข int และ bigintสามารถโยนเป็นเงินได้ ไม่แนะนำให้ใช้ตัวเลขทศนิยมเพื่อจัดการกับเงินเนื่องจากอาจเกิดข้อผิดพลาดในการปัดเศษ

ชื่อ	ขนาดการจัดเก็บ	คำอธิบาย	พิสัย
เงิน	8 ไบต์	จำนวนเงิน	-92233720368547758.08 ถึง +92233720368547758.07

ประเภทตัวละคร

ตารางด้านล่างแสดงประเภทอักขระที่ใช้งานทั่วไปที่มีอยู่ใน PostgreSQL

ส. เลขที่	ชื่อและคำอธิบาย
1	character varying(n), varchar(n) ความยาวผันแปรพร้อมขีด จำกัด
2	character(n), char(n) ความยาวคงที่เบาะว่าง
3	text ความยาวไม่ จำกัด ตัวแปร

ประเภทข้อมูลไบนารี

byteaชนิดข้อมูลช่วยให้การจัดเก็บข้อมูลของสตริงไบนารีดังแสดงในตารางด้านล่าง

ชื่อ	ขนาดการจัดเก็บ	คำอธิบาย
bytea	1 หรือ 4 ไบต์บวกสตริงไบนารีจริง	สตริงไบนารีความยาวผันแปร

ประเภทวันที่ / เวลา

PostgreSQL รองรับประเภทวันที่และเวลาของ SQL เต็มรูปแบบดังแสดงในตารางด้านล่าง วันที่จะนับตามปฏิทินเกรกอเรียน ที่นี่ทุกประเภทมีความละเอียดของ1 microsecond / 14 digits ยกเว้น date ชนิดซึ่งมีความละเอียดคือ day.

ชื่อ	ขนาดการจัดเก็บ	คำอธิบาย	มูลค่าต่ำ	มูลค่าสูง
การประทับเวลา [(p)] [ไม่มีเขตเวลา]	8 ไบต์	ทั้งวันที่และเวลา (ไม่มีเขตเวลา)	4713 ปีก่อนคริสตกาล	ค.ศ. 294276
TIMESTAMPTZ	8 ไบต์	ทั้งวันที่และเวลาพร้อมเขตเวลา	4713 ปีก่อนคริสตกาล	ค.ศ. 294276
วันที่	4 ไบต์	วันที่ (ไม่มีช่วงเวลาของวัน)	4713 ปีก่อนคริสตกาล	ค.ศ. 5874897
เวลา [(p)] [ไม่มีเขตเวลา]	8 ไบต์	ช่วงเวลาของวัน (ไม่มีวันที่)	00:00:00 น	24:00:00 น
เวลา [(p)] พร้อมเขตเวลา	12 ไบต์	ช่วงเวลาของวันเท่านั้นพร้อมเขตเวลา	00: 00: 00 + 1459	24: 00: 00-1459
ช่วงเวลา [ฟิลด์] [(p)]	12 ไบต์	ช่วงเวลา	-178000000 ปี	178000000 ปี

ประเภทบูลีน

PostgreSQL จัดเตรียม Boolean ประเภท SQL มาตรฐาน บูลีนชนิดข้อมูลสามารถมีรัฐจริง , เท็จและเป็นรัฐที่สามที่ไม่รู้จักซึ่งเป็นตัวแทนโดยค่า null SQL

ชื่อ	ขนาดการจัดเก็บ	คำอธิบาย
บูลีน	1 ไบต์	สถานะเป็นจริงหรือเท็จ

ประเภทแจกแจง

ประเภท Enumerated (enum) คือชนิดข้อมูลที่ประกอบด้วยชุดค่าคงที่ตามลำดับ เทียบเท่ากับประเภท enum ที่รองรับในภาษาโปรแกรมหลายภาษา

ไม่เหมือนกับประเภทอื่น ๆ ประเภท Enumerated ต้องสร้างโดยใช้คำสั่ง CREATE TYPE ประเภทนี้ใช้เพื่อจัดเก็บชุดค่าคงที่ตามลำดับ ตัวอย่างเช่นทิศทางของเข็มทิศเช่นทิศตะวันตกเฉียงใต้ทิศตะวันออกและทิศตะวันตกหรือวันในสัปดาห์ดังที่แสดงด้านล่าง -

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

แจกแจงสร้างครั้งเดียวใช้ได้เหมือนชนิดอื่น ๆ

ประเภทเรขาคณิต

ชนิดข้อมูลทางเรขาคณิตแสดงถึงวัตถุเชิงพื้นที่สองมิติ ประเภทพื้นฐานที่สุดประเด็นเป็นพื้นฐานสำหรับประเภทอื่น ๆ ทั้งหมด

ชื่อ	ขนาดการจัดเก็บ	การเป็นตัวแทน	คำอธิบาย
จุด	16 ไบต์	ชี้ไปที่เครื่องบิน	(x, y)
ไลน์	32 ไบต์	เส้นไม่มีที่สิ้นสุด (ไม่ได้ใช้งานอย่างสมบูรณ์)	((x1, y1), (x2, y2))
lseg	32 ไบต์	ส่วนของเส้นตรง จำกัด	((x1, y1), (x2, y2))
กล่อง	32 ไบต์	กล่องสี่เหลี่ยม	((x1, y1), (x2, y2))
เส้นทาง	16 + 16n ไบต์	เส้นทางปิด (คล้ายกับรูปหลายเหลี่ยม)	((x1, y1), ... )
เส้นทาง	16 + 16n ไบต์	เปิดเส้นทาง	[(x1, y1), ... ]
รูปหลายเหลี่ยม	40 + 16 น	รูปหลายเหลี่ยม (คล้ายกับเส้นทางปิด)	((x1, y1), ... )
วงกลม	24 ไบต์	วงกลม	<(x, y), r> (จุดศูนย์กลางและรัศมี)

ประเภทที่อยู่เครือข่าย

PostgreSQL เสนอประเภทข้อมูลเพื่อจัดเก็บที่อยู่ IPv4, IPv6 และ MAC ควรใช้ประเภทเหล่านี้แทนประเภทข้อความธรรมดาในการจัดเก็บที่อยู่เครือข่ายเนื่องจากประเภทเหล่านี้มีการตรวจสอบข้อผิดพลาดในการป้อนข้อมูลและตัวดำเนินการและฟังก์ชันพิเศษ

ชื่อ	ขนาดการจัดเก็บ	คำอธิบาย
cidr	7 หรือ 19 ไบต์	เครือข่าย IPv4 และ IPv6
inet	7 หรือ 19 ไบต์	โฮสต์และเครือข่าย IPv4 และ IPv6
macaddr	6 ไบต์	ที่อยู่ MAC

ประเภทสตริงบิต

ประเภทสตริงบิตใช้ในการจัดเก็บมาสก์บิต ซึ่งเป็น 0 หรือ 1 มีบิต SQL สองประเภท:bit(n) และ bit varying(n)โดยที่ n คือจำนวนเต็มบวก

ประเภทการค้นหาข้อความ

ประเภทนี้รองรับการค้นหาข้อความแบบเต็มซึ่งเป็นกิจกรรมของการค้นหาผ่านชุดเอกสารภาษาธรรมชาติเพื่อค้นหาเอกสารที่ตรงกับข้อความค้นหามากที่สุด มีข้อมูลสองประเภทสำหรับสิ่งนี้ -

ส. เลขที่	ชื่อและคำอธิบาย
1	tsvector นี่คือรายการเรียงลำดับของคำที่แตกต่างกันซึ่งได้รับการทำให้เป็นมาตรฐานเพื่อรวมรูปแบบต่างๆของคำเดียวกันซึ่งเรียกว่า
2	tsquery นี้จัดเก็บคำศัพท์ที่ต้องค้นหาและรวมคำศัพท์เหล่านี้ไว้เพื่อเคารพตัวดำเนินการบูลีน & (AND), | (หรือ) และ! (ไม่). วงเล็บสามารถใช้เพื่อบังคับใช้การจัดกลุ่มของตัวดำเนินการ

ประเภท UUID

UUID (Universally Unique Identifiers) เขียนเป็นลำดับของเลขฐานสิบหกตัวพิมพ์เล็กในหลาย ๆ กลุ่มคั่นด้วยยัติภังค์โดยเฉพาะกลุ่มของตัวเลขแปดหลักตามด้วยสามกลุ่มสี่หลักตามด้วยกลุ่ม 12 หลักสำหรับ จำนวน 32 หลักแทน 128 บิต

ตัวอย่างของ UUID คือ - 550e8400-e29b-41d4-a716-446655440000

ประเภท XML

ชนิดข้อมูล XML สามารถใช้เพื่อจัดเก็บข้อมูล XML สำหรับการจัดเก็บข้อมูล XML ก่อนอื่นคุณต้องสร้างค่า XML โดยใช้ฟังก์ชัน xmlparse ดังนี้ -

XMLPARSE (DOCUMENT '<?xml version="1.0"?>
<tutorial>
<title>PostgreSQL Tutorial </title>
   <topics>...</topics>
</tutorial>')

XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

ประเภท JSON

JSONชนิดข้อมูลสามารถนำมาใช้ในการจัดเก็บข้อมูล JSON (JavaScript Object สัญลักษณ์) ข้อมูลดังกล่าวสามารถจัดเก็บเป็นข้อความได้เช่นกัน แต่ชนิดข้อมูลjsonมีข้อดีในการตรวจสอบว่าค่าที่จัดเก็บแต่ละค่าเป็นค่า JSON ที่ถูกต้อง นอกจากนี้ยังมีฟังก์ชันการสนับสนุนที่เกี่ยวข้องซึ่งสามารถใช้โดยตรงเพื่อจัดการกับประเภทข้อมูล JSON ดังต่อไปนี้

ตัวอย่าง	ตัวอย่างผลลัพธ์
array_to_json ('{{1,5}, {99,100}}' :: int [])	[[1,5], [99,100]]
row_to_json (แถว (1, 'foo'))	{"f1": 1, "f2": "foo"}

ประเภทอาร์เรย์

PostgreSQL เปิดโอกาสให้กำหนดคอลัมน์ของตารางเป็นอาร์เรย์หลายมิติที่มีความยาวตัวแปร สามารถสร้างอาร์เรย์ของชนิดฐานในตัวหรือชนิดของ enum หรือประเภทคอมโพสิตที่กำหนดเองได้

การประกาศอาร์เรย์

ประเภทอาร์เรย์สามารถประกาศเป็น

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer[],
   scheme text[][]
);

หรือโดยใช้คำหลัก "ARRAY" เป็น

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer ARRAY[4],
   scheme text[][]
);

การแทรกค่า

ค่าอาร์เรย์สามารถแทรกเป็นค่าคงที่ตามตัวอักษรโดยใส่ค่าองค์ประกอบไว้ในวงเล็บปีกกาและคั่นด้วยเครื่องหมายจุลภาค ตัวอย่างแสดงด้านล่าง -

INSERT INTO monthly_savings 
VALUES (‘Manisha’, 
‘{20000, 14600, 23500, 13250}’, 
‘{{“FD”, “MF”}, {“FD”, “Property”}}’);

การเข้าถึงอาร์เรย์

ตัวอย่างสำหรับการเข้าถึง Arrays แสดงอยู่ด้านล่าง คำสั่งที่ระบุด้านล่างนี้จะเลือกบุคคลที่มีเงินออมในไตรมาสที่สองมากกว่าไตรมาสที่สี่

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];

การปรับเปลี่ยนอาร์เรย์

ตัวอย่างการแก้ไขอาร์เรย์มีดังที่แสดงด้านล่าง

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha';

หรือใช้ไวยากรณ์ของนิพจน์ ARRAY -

UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';

กำลังค้นหาอาร์เรย์

ตัวอย่างการค้นหาอาร์เรย์มีดังที่แสดงด้านล่าง

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;

หากทราบขนาดของอาร์เรย์สามารถใช้วิธีการค้นหาที่ระบุข้างต้นได้ อื่น ๆ ตัวอย่างต่อไปนี้แสดงวิธีการค้นหาเมื่อไม่ทราบขนาด

SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);

ประเภทคอมโพสิต

ประเภทนี้แสดงถึงรายชื่อเขตข้อมูลและชนิดข้อมูลกล่าวคือโครงสร้างของแถวหรือระเบียนของตาราง

การประกาศประเภทคอมโพสิต

ตัวอย่างต่อไปนี้แสดงวิธีการประกาศประเภทคอมโพสิต

CREATE TYPE inventory_item AS (
   name text,
   supplier_id integer,
   price numeric
);

ชนิดข้อมูลนี้สามารถใช้ในตารางสร้างดังต่อไปนี้ -

CREATE TABLE on_hand (
   item inventory_item,
   count integer
);

การป้อนค่าคอมโพสิต

ค่าคอมโพสิตสามารถแทรกเป็นค่าคงที่ตามตัวอักษรโดยใส่ค่าฟิลด์ไว้ในวงเล็บและคั่นด้วยเครื่องหมายจุลภาค ตัวอย่างแสดงด้านล่าง -

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

สิ่งนี้ถูกต้องสำหรับรายการสินค้าคงคลังที่กำหนดไว้ด้านบน คีย์เวิร์ด ROW เป็นทางเลือกได้จริงตราบเท่าที่คุณมีมากกว่าหนึ่งฟิลด์ในนิพจน์

การเข้าถึงประเภทคอมโพสิต

ในการเข้าถึงช่องของคอลัมน์ผสมให้ใช้จุดตามด้วยชื่อเขตข้อมูลเช่นเดียวกับการเลือกเขตข้อมูลจากชื่อตาราง ตัวอย่างเช่นในการเลือกเขตข้อมูลย่อยบางส่วนจากตารางตัวอย่างบนมือของเราแบบสอบถามจะเป็นดังที่แสดงด้านล่าง -

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

คุณยังสามารถใช้ชื่อตารางได้เช่นกัน (ตัวอย่างเช่นในการสืบค้นหลายรายการ) เช่นนี้ -

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

ประเภทช่วง

ประเภทช่วงแสดงถึงชนิดข้อมูลที่ใช้ช่วงข้อมูล ประเภทช่วงสามารถเป็นช่วงที่ไม่ต่อเนื่อง (เช่นค่าจำนวนเต็ม 1 ถึง 10 ทั้งหมด) หรือช่วงต่อเนื่อง (เช่นช่วงเวลาใด ๆ ระหว่าง 10.00 น. ถึง 11.00 น.)

ประเภทช่วงในตัวที่มีให้ใช้งาน ได้แก่ ช่วงต่อไปนี้ -

• int4range - ช่วงของจำนวนเต็ม

• int8range - ช่วงของ bigint

• numrange - ช่วงของตัวเลข

• tsrange - ช่วงของการประทับเวลาโดยไม่มีเขตเวลา

• tstzrange - ช่วงของการประทับเวลากับเขตเวลา

• daterange - ช่วงวันที่

คุณสามารถสร้างประเภทช่วงที่กำหนดเองเพื่อให้มีช่วงประเภทใหม่ ๆ เช่นช่วงที่อยู่ IP โดยใช้ประเภท inet เป็นฐานหรือช่วงลอยโดยใช้ประเภทข้อมูลลอยเป็นฐาน

ประเภทช่วงรองรับขอบเขตช่วงที่รวมและพิเศษโดยใช้อักขระ [] และ () ตามลำดับ ตัวอย่างเช่น "[4,9)" แทนจำนวนเต็มทั้งหมดที่เริ่มจากและรวม 4 ถึง แต่ไม่รวม 9

ประเภทตัวระบุวัตถุ

ตัวระบุออบเจ็กต์ (OID) ถูกใช้ภายในโดย PostgreSQL เป็นคีย์หลักสำหรับตารางระบบต่างๆ หากระบุด้วย OIDSหรือเปิดใช้งานตัวแปรคอนฟิกูเรชันdefault_with_oidsเฉพาะในกรณีเช่นนี้จะเพิ่ม OID ลงในตารางที่ผู้ใช้สร้างขึ้น ตารางต่อไปนี้แสดงนามแฝงหลายประเภท ชนิดนามแฝง OID ไม่มีการดำเนินการของตนเองยกเว้นรูทีนอินพุตและเอาต์พุตพิเศษ

ชื่อ	อ้างอิง	คำอธิบาย	ตัวอย่างค่า
oid	ใด ๆ	ตัวระบุวัตถุที่เป็นตัวเลข	564182
regproc	pg_proc	ชื่อฟังก์ชัน	ผลรวม
regprocedure	pg_proc	ฟังก์ชันที่มีประเภทอาร์กิวเมนต์	ผลรวม (int4)
regoper	pg_operator	ชื่อผู้ดำเนินการ	+
ผู้ควบคุม	pg_operator	ตัวดำเนินการที่มีประเภทอาร์กิวเมนต์	* (จำนวนเต็มจำนวนเต็ม) หรือ - (ไม่มีจำนวนเต็ม)
regclass	pg_class	ชื่อความสัมพันธ์	pg_type
regtype	pg_type	ชื่อชนิดข้อมูล	จำนวนเต็ม
regconfig	pg_ts_config	การกำหนดค่าการค้นหาข้อความ	ภาษาอังกฤษ
regdictionary	pg_ts_dict	พจนานุกรมค้นหาข้อความ	เรียบง่าย

ประเภทหลอก

ระบบชนิด PostgreSQL ประกอบด้วยรายการวัตถุประสงค์พิเศษจำนวนหนึ่งซึ่งเรียกรวมกันว่าประเภทหลอก ไม่สามารถใช้ประเภทหลอกเป็นชนิดข้อมูลคอลัมน์ แต่สามารถใช้เพื่อประกาศอาร์กิวเมนต์ของฟังก์ชันหรือประเภทผลลัพธ์ได้

ตารางด้านล่างแสดงรายการประเภทหลอกที่มีอยู่

ส. เลขที่	ชื่อและคำอธิบาย
1	any ระบุว่าฟังก์ชันยอมรับชนิดข้อมูลอินพุตใด ๆ
2	anyelement ระบุว่าฟังก์ชันยอมรับชนิดข้อมูลใด ๆ
3	anyarray ระบุว่าฟังก์ชันยอมรับชนิดข้อมูลอาร์เรย์ใด ๆ
4	anynonarray ระบุว่าฟังก์ชันยอมรับชนิดข้อมูลที่ไม่ใช่อาร์เรย์
5	anyenum ระบุว่าฟังก์ชันยอมรับชนิดข้อมูล enum ใด ๆ
6	anyrange ระบุว่าฟังก์ชันยอมรับชนิดข้อมูลช่วงใด ๆ
7	cstring ระบุว่าฟังก์ชันยอมรับหรือส่งคืนสตริง C ที่สิ้นสุดด้วยค่า null
8	internal บ่งชี้ว่าฟังก์ชันยอมรับหรือส่งคืนชนิดข้อมูลภายในเซิร์ฟเวอร์
9	language_handler ตัวจัดการการเรียกภาษาขั้นตอนถูกประกาศให้ส่งคืน language_handler
10	fdw_handler ตัวจัดการ Wrapper ข้อมูลแปลกปลอมถูกประกาศให้ส่งคืน fdw_handler
11	record ระบุฟังก์ชันที่ส่งคืนประเภทแถวที่ไม่ระบุ
12	trigger มีการประกาศฟังก์ชันทริกเกอร์เพื่อส่งคืนทริกเกอร์
13	void ระบุว่าฟังก์ชันไม่ส่งคืนค่า