คำสั่ง PostgreSQL พื้นฐาน คำสั่ง PostgreSQL พื้นฐานในการค้นหาและเปลี่ยนตำแหน่งของอินสแตนซ์คลัสเตอร์

สวัสดี วันนี้ฉันต้องการสร้างบันทึกย่อเกี่ยวกับคำสั่ง PostgreSQL พื้นฐาน คุณสามารถทำงานกับ PosgreSQL ได้ทั้งแบบโต้ตอบและจากบรรทัดคำสั่ง โปรแกรมเป็น psql ฉันแน่ใจว่ารายการนี้จะเป็นประโยชน์กับคุณมากและจะช่วยคุณประหยัดเวลาในการค้นหาแหล่งข้อมูลต่างๆ ฉันขอเตือนคุณว่านี่เป็นโครงการโอเพ่นซอร์สตาม Postgres DBMS ซึ่งเปิดตัวในปี 2529 ได้รับการพัฒนาโดยกลุ่มนักพัฒนา PGDG ทั่วโลกโดยพื้นฐานแล้วคือ 5-8 คน แต่ถึงกระนั้นก็ตาม พัฒนาอย่างเข้มข้น แนะนำฟังก์ชั่นใหม่ทั้งหมดและแก้ไขจุดบกพร่องและข้อผิดพลาดเก่า

คำสั่งพื้นฐาน PostgreSQL ในโหมดโต้ตอบ:

• \ connect db_name - เชื่อมต่อกับฐานข้อมูลชื่อ db_name
• \ du - รายชื่อผู้ใช้
• \ dp (หรือ \ z) - รายการตาราง, มุมมอง, ลำดับ, สิทธิ์การเข้าถึง
• \ di - ดัชนี
• \ ds - ลำดับ
• \ dt - รายการตาราง
• \ dt + - รายการตารางทั้งหมดพร้อมคำอธิบาย
• \ dt * s * - รายการตารางทั้งหมดที่มี s ในชื่อ
• \ dv - มุมมอง
• \ dS - ตารางระบบ
• \ d + - คำอธิบายตาราง
• \ o - ส่งผลการค้นหาไปยังไฟล์
• \ l - รายการฐานข้อมูล
• \ i - อ่านข้อมูลขาเข้าจากไฟล์
• \ e - เปิดเนื้อหาปัจจุบันของบัฟเฟอร์คำขอในตัวแก้ไข (เว้นแต่จะระบุไว้เป็นอย่างอื่นในสภาพแวดล้อมของตัวแปร EDITOR vi จะถูกใช้โดยค่าเริ่มต้น)
• \ d “table_name” - คำอธิบายตาราง
• \ i เรียกใช้คำสั่งจากไฟล์ภายนอกเช่น \ i /my/directory/my.sql
• \ pset - คำสั่งสำหรับตั้งค่าพารามิเตอร์การจัดรูปแบบ
• \ echo - แสดงข้อความ
• \ set - ตั้งค่าตัวแปรสภาพแวดล้อม หากไม่มีพารามิเตอร์ จะแสดงรายการตัวแปรปัจจุบัน (\ unset - ลบ)
• \? - การอ้างอิง psql
• \ help - การอ้างอิง SQL
• \ q (หรือ Ctrl + D) - ออกจากโปรแกรม

การทำงานกับ PostgreSQL จากบรรทัดคำสั่ง:

• -c (หรือ -command) - เรียกใช้คำสั่ง SQL โดยไม่ต้องเข้าสู่โหมดโต้ตอบ
• -f file.sql - รันคำสั่งจาก file.sql file
• -l (หรือ -list) - แสดงรายการฐานข้อมูลที่มีอยู่
• -U (หรือ -username) - ระบุชื่อผู้ใช้ (เช่น postgres)
• -W (หรือ -รหัสผ่าน) - แจ้งรหัสผ่าน
• -d dbname - เชื่อมต่อกับฐานข้อมูล dbname
• -h - ชื่อโฮสต์ (เซิร์ฟเวอร์)
• -s - โหมดทีละขั้นตอน นั่นคือ คุณจะต้องยืนยันคำสั่งทั้งหมด
• –S - โหมดบรรทัดเดียว กล่าวคือ การขึ้นบรรทัดใหม่จะดำเนินการค้นหา (กำจัด; ที่ส่วนท้ายของคำสั่ง SQL)
• -V - เวอร์ชัน PostgreSQL โดยไม่ต้องเข้าสู่โหมดโต้ตอบ

ตัวอย่าง:

psql -U postgres -d dbname -c “CREATE TABLE my (some_id serial PRIMARY KEY, some_text text);” - การดำเนินการคำสั่งในฐานข้อมูล dbname

psql -d dbname -H -c "SELECT * FROM my" -o my.html - ส่งออกผลลัพธ์การสืบค้นไปยังไฟล์ html

โปรแกรมอรรถประโยชน์ PosgreSQL (โปรแกรม):

• createdb และ dropdb - สร้างและวางฐานข้อมูล (ตามลำดับ)
• createuser และ dropuser - สร้างและผู้ใช้ (ตามลำดับ)
• pg_ctl - โปรแกรมที่ออกแบบมาเพื่อแก้ปัญหาการจัดการทั่วไป (เริ่ม หยุด กำหนดค่าพารามิเตอร์ ฯลฯ)
• postmaster - โมดูลเซิร์ฟเวอร์ผู้ใช้หลายคน PostgreSQL (การตั้งค่าระดับการดีบัก พอร์ต ไดเรกทอรีข้อมูล)
• initdb - สร้างคลัสเตอร์ PostgreSQL ใหม่
• initlocation - โปรแกรมสำหรับสร้างไดเร็กทอรีสำหรับการจัดเก็บฐานข้อมูลสำรอง
• vacuumdb - การบำรุงรักษาฐานข้อมูลทางกายภาพและเชิงวิเคราะห์
• pg_dump - สำรองและกู้คืนข้อมูล
• pg_dumpall - สำรองข้อมูลคลัสเตอร์ PostgreSQL ทั้งหมด
• pg_restore - กู้คืนฐานข้อมูลจากไฟล์เก็บถาวร (.tar, .tar.gz)

ตัวอย่างการสร้างการสำรองข้อมูล:

การสร้างสำเนาสำรองของฐานข้อมูล mydb ในรูปแบบบีบอัด

Pg_dump -h localhost -p 5440 -U someuser -F c -b -v -f mydb.backup mydb

การสร้างสำเนาสำรองของฐานข้อมูล mydb ในรูปแบบของไฟล์ข้อความธรรมดา รวมถึงคำสั่งสร้างฐานข้อมูล

Pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

การสร้างสำเนาสำรองของฐานข้อมูล mydb ในรูปแบบบีบอัด โดยมีตารางที่มีการชำระเงินในชื่อ

Pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t * การชำระเงิน * -f payment_tables.backup mydb

ดัมพ์ข้อมูลจากตารางที่ระบุเพียงตารางเดียวเท่านั้น หากคุณต้องการสำรองข้อมูลหลายตาราง ชื่อของตารางเหล่านี้จะแสดงโดยใช้สวิตช์ -t สำหรับแต่ละตาราง

Pg_dump -a -t table_name -f file_name database_name

การสำรองข้อมูลด้วยการบีบอัด gz

Pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c> mydb.gz

รายการตัวเลือกที่ใช้บ่อยที่สุด:

• -h host - โฮสต์ หากไม่ได้ระบุไว้ จะใช้ localhost หรือค่าจากตัวแปรสภาพแวดล้อม PGHOST
• -p port - port หากไม่ได้ระบุ 5432 หรือใช้ค่าจากตัวแปรสภาพแวดล้อม PGPORT
• -u - ผู้ใช้ หากไม่ได้ระบุ ผู้ใช้ปัจจุบันจะถูกใช้ ค่านี้สามารถระบุได้ในตัวแปรสภาพแวดล้อม PGUSER
• -a, -data-only - ดัมพ์เฉพาะข้อมูล ข้อมูล และสคีมาจะถูกบันทึกตามค่าเริ่มต้น
• -b - รวมอ็อบเจ็กต์ขนาดใหญ่ (บล็อก) ในดัมพ์
• -s, -schema-only - ดัมพ์สกีมาเท่านั้น
• -C, -create - เพิ่มคำสั่งเพื่อสร้างฐานข้อมูล
• -c - เพิ่มคำสั่งสำหรับวาง (วาง) วัตถุ (ตาราง มุมมอง ฯลฯ)
• -O - ​​อย่าเพิ่มคำสั่งในการตั้งค่าเจ้าของวัตถุ (ตาราง มุมมอง ฯลฯ)
• -F, -format (c | t | p) - รูปแบบการถ่ายโอนข้อมูลเอาต์พุต, กำหนดเอง, tar หรือข้อความธรรมดา
• -t, -table = TABLE - ระบุตารางเฉพาะสำหรับการถ่ายโอนข้อมูล
• -v, -verbose - ส่งออกข้อมูลอย่างละเอียด
• -D, -attribute-inserts - ดัมพ์โดยใช้คำสั่ง INSERT พร้อมรายชื่อคุณสมบัติ

สำรองฐานข้อมูลทั้งหมดโดยใช้คำสั่ง pg_dumpall

Pg_dumpall> all.sql

การกู้คืนตารางจากการสำรองข้อมูล (สำรอง):

psql - กู้คืนข้อมูลสำรองที่เก็บไว้ในไฟล์ข้อความธรรมดา
pg_restore - กู้คืนข้อมูลสำรองที่บีบอัด (tar);

กู้คืนข้อมูลสำรองทั้งหมดโดยไม่สนใจข้อผิดพลาด

Psql -h localhost -U someuser -d dbname -f mydb.sql

กู้คืนข้อมูลสำรองทั้งหมดโดยหยุดที่ข้อผิดพลาดครั้งแรก

Psql -h localhost -U someuser -set ON_ERROR_STOP = on -f mydb.sql

ในการกู้คืนจากไฟล์เก็บถาวร tar เราต้องสร้างฐานข้อมูลโดยใช้ CREATE DATABASE mydb ก่อน (หากไม่ได้ระบุตัวเลือก -C เมื่อสร้างข้อมูลสำรอง) และกู้คืน

Pg_restore -dbname = mydb -jobs = 4 -verbose mydb.backup

การกู้คืนการสำรองฐานข้อมูลที่บีบอัด gz

psql -U postgres -d mydb -f mydb

ฉันคิดว่าฐานข้อมูล postgresql จะชัดเจนสำหรับคุณมากขึ้น ฉันหวังว่ารายการคำสั่ง PostgreSQL นี้จะเป็นประโยชน์กับคุณ

ปรับปรุงล่าสุด: 17.03.2018

ในการสร้างตาราง ให้ใช้คำสั่ง CREATE TABLE ตามด้วยชื่อตาราง นอกจากนี้ยังมีตัวดำเนินการจำนวนหนึ่งที่สามารถใช้กับคำสั่งนี้ซึ่งกำหนดคอลัมน์ของตารางและแอตทริบิวต์ของคำสั่ง ไวยากรณ์ทั่วไปสำหรับการสร้างตารางมีดังนี้:

สร้างตาราง table_name (column_name1 datatype column_ attributes1, column_name2 datatype column_ attributes2, .................................... .. .......... column_nameN ประเภทข้อมูล column_ attributesN, table_ คุณลักษณะ);

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

ตัวอย่างเช่น มาสร้างตารางในฐานข้อมูลผ่าน pgAdmin ในการดำเนินการนี้ ก่อนอื่นให้เลือกฐานข้อมูลเป้าหมายใน pgAdmin คลิกขวาและเลือกรายการเครื่องมือสืบค้นข้อมูล ... ในเมนูบริบท:

หลังจากนั้นช่องสำหรับป้อนรหัส SQL จะเปิดขึ้น นอกจากนี้ ตารางจะถูกสร้างขึ้นโดยเฉพาะสำหรับฐานข้อมูลที่เราจะเปิดฟิลด์นี้เพื่อป้อน SQL

สร้างลูกค้าตาราง (รหัสหลัก SERIAL คีย์, ชื่อตัวละครที่แตกต่างกัน (30), นามสกุลอักขระที่แตกต่างกัน (30), อีเมลตัวอักษรที่แตกต่างกัน (30), จำนวนเต็มอายุ);

ในกรณีนี้ ตารางลูกค้าจะกำหนดห้าคอลัมน์: Id, FirstName, LastName, Age, Email คอลัมน์แรก Id แสดงถึงรหัสลูกค้า ซึ่งทำหน้าที่เป็นคีย์หลัก ดังนั้นจึงเป็นประเภท SERIAL อันที่จริง คอลัมน์นี้จะเก็บค่าตัวเลข 1, 2, 3 ฯลฯ ซึ่งจะเพิ่มขึ้นทีละหนึ่งสำหรับแต่ละแถวใหม่โดยอัตโนมัติ

สามคอลัมน์ถัดไปแสดงชื่อ นามสกุล และที่อยู่อีเมลของลูกค้า และเป็นประเภทที่แตกต่างกัน (30) กล่าวคือ แสดงสตริงที่มีความยาวไม่เกิน 30 อักขระ

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

และหลังจากดำเนินการคำสั่งนี้ ตารางลูกค้าจะถูกเพิ่มไปยังฐานข้อมูลที่เลือก

วางโต๊ะ

ในการวางตาราง ให้ใช้คำสั่ง DROP TABLE ซึ่งมีรูปแบบดังนี้:

DROP Table table1 [, table2, ...];

เช่น วางโต๊ะลูกค้า

ทางเลือกอื่นนอกเหนือจากการทำงานกับฐานข้อมูลผ่านคอนโซลคือสภาพแวดล้อมที่ใช้งานง่ายกว่าสำหรับไคลเอ็นต์ใดๆ ที่มีอินเทอร์เฟซแบบกราฟิก ตัวอย่างเช่น, pgAdmin... การติดตั้งนั้นง่ายมาก เรารันด้วยสิทธิ์ superuser:

sudo apt-get ติดตั้ง pgadmin3

ตอนนี้คุณสามารถวิ่ง pgAdminผ่านอินเทอร์เฟซแบบกราฟิก เลือกเซิร์ฟเวอร์ฐานข้อมูลท้องถิ่นจากรายการ (มีพอร์ตเริ่มต้น 5432) และเพิ่มฐานข้อมูลที่เราได้สร้างไว้แล้วด้วยพารามิเตอร์ที่เราระบุ

pgAdmin

ในเซิร์ฟเวอร์ท้องถิ่นนี้นอกเหนือจากโหนด ฐานข้อมูลคุณยังสามารถหาโหนดที่เรียกว่า เข้าสู่ระบบบทบาท- บทบาทที่มีอยู่ทั้งหมด

มาเลือกจากฐานข้อมูลที่มีอยู่ทั้งหมดที่เราสร้างขึ้นด้วย ฐานข้อมูลประกอบด้วยวัตถุหลายประเภท ควรให้ความสนใจเป็นพิเศษกับตารางและลำดับ ( ลำดับ).

ส่วนใหญ่แล้ว ตารางธรรมดาจะมีคีย์หลักอยู่ในรูปแบบของค่าตัวเลขที่เป็นบวก ค่านี้ต้องไม่ซ้ำกันสำหรับแต่ละแถว ดังนั้นเพื่อไม่ให้ตั้งค่าโดยตรงในคำขอในแต่ละครั้ง คุณสามารถตั้งค่าลำดับเป็นค่าเริ่มต้นได้

ขั้นแรก มาสร้างลำดับกัน พวกเราเลือก ลำดับ - ลำดับใหม่... บนแท็บแรก ป้อนชื่อและระบุเป็น เจ้าของบทบาทที่เราได้สร้างขึ้น ลำดับนี้จะไม่พร้อมใช้งานสำหรับบทบาทอื่น บนแท็บที่สองเข้าไปในทุ่งนา เพิ่มขึ้นและ เริ่มเราป้อนทีละรายการ (เว้นแต่คุณต้องการเป็นอย่างอื่น) บนแท็บสุดท้ายของกล่องโต้ตอบ คุณสามารถดูผลลัพธ์ของแบบสอบถาม SQL ที่จะดำเนินการกับฐานข้อมูล

หลังจากสร้างลำดับแล้ว มาเริ่มสร้างตารางกัน นอกจากนี้เรายังระบุชื่อและเจ้าของ (เจ้าของ) ของเธอด้วย บนแท็บที่สี่ คอลัมน์เราเพิ่มคีย์หลักก่อน ปุ่ม เพิ่มในหน้าต่างที่ปรากฏขึ้น ให้ระบุชื่อคอลัมน์ เช่น NS... เราเลือกเป็นประเภทข้อมูล bigint... ในแท็บที่สอง คำนิยามในสนาม ค่าเริ่มต้นเราระบุลำดับของเรา ฟิลด์ต้องมีค่าของ form nextval ("message_id_seq" :: regclass)... นั่นคือ ทุกครั้งที่มีการเพิ่มบรรทัดใหม่ ค่าถัดไปจะถูกนำมาจากลำดับ เพิ่มคอลัมน์อื่นๆ ตามต้องการ สุดท้ายในแท็บ ข้อจำกัดเพิ่มข้อจำกัดในคีย์หลัก ( คีย์หลัก). ในแท็บสุดท้าย เราสามารถชื่นชมผลลัพธ์โค้ด SQL ที่ pgAdmin สร้างขึ้น หลังจากคลิกตกลง ตารางจะถูกสร้างขึ้น

แทนที่จะเป็น bigint เป็นคีย์หลัก คุณสามารถระบุเป็นประเภทคอลัมน์ได้ bigserial... ประเภทนี้จะเพิ่มขึ้นโดยอัตโนมัติเมื่อมีการเพิ่มแถวใหม่ทุกแถว ดังนั้นจึงไม่จำเป็นต้องสร้างลำดับสำหรับแถวใหม่ นั่นคือ ในกรณีที่ง่ายที่สุด หากคุณไม่มีกฎพิเศษใดๆ ในการสร้าง ID ระเบียน คุณสามารถแนะนำให้ใช้ bigserial

มาดูเนื้อหาของตารางกัน เมื่อต้องการทำสิ่งนี้ ให้คลิกขวาที่มันแล้วเลือกจากเมนูบริบท ดูข้อมูล - ดู 100 แถว.

ในหน้าต่างเดียวกัน คุณสามารถแก้ไขค่าของเซลล์ในตารางได้อย่างรวดเร็ว หากตารางของคุณมีระเบียนมากกว่า 100 รายการ ให้เปลี่ยนจำนวนระเบียนที่แสดงในรายการดรอปดาวน์ที่ด้านบนของหน้าต่าง คุณสามารถแสดง 100, 500, 1000 หรือระเบียนทั้งหมด แต่นี่ไม่ใช่อะไรมากไปกว่าการดูเนื้อหาของตารางอย่างรวดเร็วและคุณไม่ควรชินกับมัน หากคุณมีระเบียนหลายหมื่นรายการในตาราง ฉันไม่แนะนำให้แสดงระเบียนทั้งหมดในคราวเดียว ในกรณีนี้ จะเป็นการดีกว่าถ้าเขียนแบบสอบถามโดยใช้พารามิเตอร์ ขีดจำกัดและ offset.

15 คำสั่ง PostgreSQL ที่มีประโยชน์

มีแบบฝึกหัด PostgreSQL มากมายบนเน็ตที่อธิบายคำสั่งพื้นฐาน แต่เมื่อคุณเจาะลึกลงไปในงาน ปัญหาในทางปฏิบัติก็เกิดขึ้นซึ่งต้องใช้ทีมขั้นสูง

คำสั่งหรือตัวอย่างข้อมูลดังกล่าวไม่ค่อยได้รับการจัดทำเป็นเอกสาร มาดูตัวอย่างกัน ซึ่งมีประโยชน์สำหรับทั้งนักพัฒนาและผู้ดูแลระบบฐานข้อมูล

การรับข้อมูลเกี่ยวกับฐานข้อมูล

ขนาดฐานข้อมูล

เพื่อให้ได้ขนาดจริงของไฟล์ (ที่เก็บข้อมูล) ของฐานข้อมูล เราใช้แบบสอบถามต่อไปนี้:

เลือก pg_database_size (current_database ());

ผลลัพธ์จะแสดงเป็นตัวเลขเช่น 41809016

current_database () เป็นฟังก์ชันที่ส่งคืนชื่อของฐานข้อมูลปัจจุบัน คุณสามารถป้อนชื่อในข้อความแทน:

เลือก pg_database_size ("my_database");

เพื่อให้ได้ข้อมูลในรูปแบบที่มนุษย์อ่านได้ เราใช้ฟังก์ชัน pg_size_pretty:

เลือก pg_size_pretty (pg_database_size (current_database ()));

เป็นผลให้เราได้รับข้อมูลของแบบฟอร์ม 40 Mb

รายชื่อโต๊ะ

บางครั้งคุณต้องการรับรายการตารางฐานข้อมูล เมื่อต้องการทำเช่นนี้ ใช้แบบสอบถามต่อไปนี้:

เลือก table_name จาก information_schema.tables โดยที่ table_schema ไม่ได้อยู่ใน ("information_schema", "pg_catalog");

information_schema เป็นสคีมาฐานข้อมูลมาตรฐานที่มีคอลเลกชันของมุมมอง เช่น ตาราง ฟิลด์ ฯลฯ มุมมองตารางมีข้อมูลเกี่ยวกับตารางทั้งหมดในฐานข้อมูล

แบบสอบถามที่อธิบายด้านล่างจะเลือกตารางทั้งหมดจากสคีมาที่ระบุของฐานข้อมูลปัจจุบัน:

เลือก table_name จาก information_schema.tables โดยที่ table_schema ไม่อยู่ใน ("information_schema", "pg_catalog") และ table_schema IN ("สาธารณะ", "myschema");

ในส่วนคำสั่ง IN สุดท้าย คุณสามารถระบุชื่อของสคีมาเฉพาะได้

ขนาดโต๊ะ

โดยการเปรียบเทียบกับการหาขนาดของฐานข้อมูล ขนาดของข้อมูลตารางสามารถคำนวณได้โดยใช้ฟังก์ชันที่เกี่ยวข้อง:

เลือก pg_relation_size ("บัญชี");

ฟังก์ชัน pg_relation_size ส่งคืนจำนวนพื้นที่ที่เลเยอร์ที่ระบุของตารางหรือดัชนีที่ระบุใช้อยู่บนดิสก์

ชื่อโต๊ะที่ใหญ่ที่สุด

เพื่อแสดงรายการของตารางในฐานข้อมูลปัจจุบัน เรียงลำดับตามขนาดตาราง ดำเนินการแบบสอบถามต่อไปนี้:

เลือก relname, relpages จาก pg_class ORDER BY relpages DESC;

เพื่อแสดงข้อมูลเกี่ยวกับตารางที่ใหญ่ที่สุด เราจำกัดการสืบค้นโดยใช้ LIMIT:

SELECT relname, relpages จาก pg_class ORDER BY relpages DESC LIMIT 1;

relname คือชื่อของตาราง ดัชนี มุมมอง ฯลฯ
relpages - ขนาดของมุมมองบนดิสก์ของตารางนี้เป็นจำนวนหน้า (โดยค่าเริ่มต้น หนึ่งหน้าคือ 8 KB)
pg_class คือตารางระบบที่มีข้อมูลเกี่ยวกับความสัมพันธ์ระหว่างตารางฐานข้อมูล

รายชื่อผู้ใช้ที่เชื่อมต่อ

เมื่อต้องการค้นหาชื่อ IP และพอร์ตที่ใช้ของผู้ใช้ที่เชื่อมต่อ ให้เรียกใช้แบบสอบถามต่อไปนี้:

เลือก datname, usename, client_addr, client_port จาก pg_stat_activity;

กิจกรรมของผู้ใช้

ในการค้นหากิจกรรมการเชื่อมต่อของผู้ใช้รายใดรายหนึ่ง เราใช้แบบสอบถามต่อไปนี้:

เลือก datname จาก pg_stat_activity โดยที่ usename = "devuser";

การทำงานกับเขตข้อมูลและตาราง

การลบบรรทัดที่ซ้ำกัน

หากเกิดขึ้นโดยที่ตารางไม่มีคีย์หลัก ก็มีแนวโน้มว่าจะมีรายการซ้ำกันในระเบียน หากสำหรับตารางดังกล่าว โดยเฉพาะอย่างยิ่งตารางขนาดใหญ่ จำเป็นต้องตั้งค่าข้อจำกัดเพื่อตรวจสอบความสมบูรณ์ จากนั้นเราจะลบองค์ประกอบต่อไปนี้:

• เส้นที่ซ้ำกัน
• สถานการณ์เมื่อมีการทำซ้ำคอลัมน์อย่างน้อยหนึ่งคอลัมน์ (หากควรใช้คอลัมน์เหล่านี้เป็นคีย์หลัก)

พิจารณาตารางที่มีข้อมูลลูกค้า โดยจะทำซ้ำทั้งแถว (แถวที่สองในแถว)

แบบสอบถามต่อไปนี้จะช่วยลบรายการที่ซ้ำกันทั้งหมด:

ลบจากลูกค้าโดยที่ ctid ไม่อยู่ใน (เลือกสูงสุด (ctid) จากกลุ่มลูกค้าตามลูกค้า *);

ฟิลด์ ctid ซึ่งไม่ซ้ำกันสำหรับแต่ละเร็กคอร์ด จะถูกซ่อนไว้โดยค่าเริ่มต้น แต่มีอยู่ในทุกตาราง

คำขอสุดท้ายใช้ทรัพยากรมาก ดังนั้นโปรดใช้ความระมัดระวังเมื่อดำเนินการกับโปรเจ็กต์ที่ใช้งานจริง

ตอนนี้ให้พิจารณากรณีที่ค่าของฟิลด์ซ้ำ

หากสามารถลบรายการที่ซ้ำกันโดยไม่บันทึกข้อมูลทั้งหมด เราจะดำเนินการค้นหาต่อไปนี้:

ลบจากลูกค้าโดยที่ ctid ไม่อยู่ใน (เลือกสูงสุด (ctid) จากกลุ่มลูกค้าโดย customer_id);

หากข้อมูลมีความสำคัญ ก่อนอื่นคุณต้องค้นหาระเบียนที่ซ้ำกัน:

เลือก * จากลูกค้าโดยที่ ctid ไม่อยู่ใน (เลือกสูงสุด (ctid) จากกลุ่มลูกค้าโดย customer_id);

ก่อนลบเรกคอร์ดดังกล่าว คุณสามารถย้ายไปยังตารางชั่วคราวหรือแทนที่ค่า customer_id ในเรกคอร์ดเหล่านั้นด้วยค่าอื่น

รูปแบบทั่วไปของคำขอให้ลบบันทึกที่อธิบายข้างต้นมีดังนี้:

ลบออกจาก table_name โดยที่ ctid ไม่ได้อยู่ใน (เลือกสูงสุด (ctid) จาก table_name GROUP BY column1,);

เปลี่ยนประเภทฟิลด์อย่างปลอดภัย

คำถามอาจเกิดขึ้นเกี่ยวกับการรวมงานดังกล่าวในรายการนี้ อันที่จริงใน PostgreSQL นั้นง่ายมากที่จะเปลี่ยนประเภทของฟิลด์โดยใช้คำสั่ง ALTER ลองดูที่ตารางลูกค้าอีกครั้งเป็นตัวอย่าง

ฟิลด์ customer_id ใช้ชนิดข้อมูลสตริง varchar นี่เป็นข้อผิดพลาดเนื่องจากฟิลด์นี้ควรจะเก็บรหัสลูกค้าซึ่งอยู่ในรูปแบบจำนวนเต็ม การใช้ varchar นั้นไม่สมเหตุสมผล ลองแก้ไขความเข้าใจผิดนี้โดยใช้คำสั่ง ALTER:

ALTER TABLE ลูกค้า ALTER COLUMN customer_id TYPE integer;

แต่จากการดำเนินการเราได้รับข้อผิดพลาด:

ข้อผิดพลาด: ไม่สามารถส่งคอลัมน์ “customer_id” โดยอัตโนมัติเพื่อพิมพ์จำนวนเต็ม
สถานะ SQL: 42804
คำแนะนำ: ระบุนิพจน์ USING เพื่อทำการแปลง

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

ALTER TABLE ลูกค้า ALTER COLUMN customer_id TYPE integer USING (customer_id :: integer);

เป็นผลให้ทุกอย่างดำเนินไปโดยไม่มีข้อผิดพลาด:

โปรดทราบว่าเมื่อใช้ USING นอกเหนือจากนิพจน์เฉพาะแล้ว ยังสามารถใช้ฟังก์ชัน ฟิลด์อื่นๆ และตัวดำเนินการได้

ตัวอย่างเช่น ลองแปลงฟิลด์ customer_id กลับเป็น varchar แต่ด้วยการแปลงรูปแบบข้อมูล:

ALTER TABLE ลูกค้า ALTER COLUMN customer_id TYPE varchar USING (customer_id || "-" || first_name);

เป็นผลให้ตารางจะมีลักษณะดังนี้:

หาค่า "หาย"

โปรดใช้ความระมัดระวังเมื่อใช้ลำดับเป็นคีย์หลัก: เมื่อกำหนด องค์ประกอบบางอย่างของลำดับจะถูกข้ามไปโดยไม่ได้ตั้งใจ และเนื่องจากการทำงานกับตาราง ระเบียนบางรายการจะถูกลบออก ค่าเหล่านี้สามารถใช้ได้อีกครั้ง แต่หาได้ยากในตารางขนาดใหญ่

พิจารณาสองตัวเลือกการค้นหา

วิธีแรก
ดำเนินการค้นหาต่อไปนี้เพื่อค้นหาจุดเริ่มต้นของช่วงเวลาที่มีค่า "สูญหาย":

SELECT customer_id + 1 จากลูกค้า mo ไม่มีอยู่ (เลือก NULL จากลูกค้า mi WHERE mi.customer_id = mo.customer_id + 1) ORDER BY customer_id;

เป็นผลให้เราได้รับค่า: 5, 9 และ 11

หากคุณต้องการค้นหาไม่เพียงแค่การเกิดขึ้นครั้งแรก แต่ยังรวมถึงค่าที่หายไปทั้งหมด เราใช้แบบสอบถาม (เน้นทรัพยากรมาก!) ต่อไปนี้:

C seq_max AS (เลือก max (customer_id) จากลูกค้า), seq_min AS (เลือก min (customer_id) จากลูกค้า) SELECT * FROM generate_series ((SELECT min FROM seq_min), (SELECT max FROM seq_max)) ยกเว้น SELECT customer_id จากลูกค้า;

เป็นผลให้เราเห็นผลลัพธ์ต่อไปนี้: 5, 9 และ 6

วิธีที่สอง
เราได้รับชื่อของลำดับที่เกี่ยวข้องกับ customer_id:

เลือก pg_get_serial_sequence ("ลูกค้า", "customer_id");

และเราพบตัวระบุที่ขาดหายไปทั้งหมด:

C sequence_info AS (เลือก start_value, last_value จาก "SchemaName". "SequenceName") SELECT generate_series ((sequence_info.start_value), (sequence_info.last_value)) FROM sequence_info ยกเว้น SELECT customer_id จากลูกค้า

การนับจำนวนแถวในตาราง

จำนวนแถวคำนวณโดยฟังก์ชันการนับมาตรฐาน แต่สามารถใช้กับเงื่อนไขเพิ่มเติมได้

จำนวนแถวทั้งหมดในตาราง:

เลือกจำนวน (*) จากตาราง;

จำนวนแถวที่มีเงื่อนไขว่าฟิลด์ที่ระบุไม่มี NULL:

เลือกจำนวน (col_name) จากตาราง;

จำนวนบรรทัดที่ไม่ซ้ำสำหรับฟิลด์ที่ระบุ:

เลือกจำนวน (col_name ที่แตกต่าง) จากตาราง;

การใช้ธุรกรรม

ธุรกรรมรวมลำดับของการกระทำเข้าเป็นการดำเนินการเดียว ลักษณะเฉพาะของมันคือหากมีข้อผิดพลาดในการทำธุรกรรมจะไม่มีการบันทึกผลลัพธ์ของการกระทำใด ๆ ในฐานข้อมูล

เริ่มต้นการทำธุรกรรมโดยใช้คำสั่ง BEGIN

ในการย้อนกลับการดำเนินการทั้งหมดหลังจาก BEGIN ให้ใช้คำสั่ง ROLLBACK

และเพื่อนำไปใช้ - คำสั่ง COMMIT

การดูและดำเนินการตามคำขอที่ดำเนินการได้สำเร็จ

ในการรับข้อมูลเกี่ยวกับคำขอ ให้รันคำสั่งต่อไปนี้:

SELECT pid, อายุ (query_start, clock_timestamp ()), usename, สืบค้นจาก pg_stat_activity ที่การสืบค้น! = " "และแบบสอบถามไม่ ILIKE"% pg_stat_activity% "เรียงลำดับตาม query_start desc;

ในการหยุดคำขอเฉพาะ ให้รันคำสั่งต่อไปนี้ โดยระบุรหัสกระบวนการ (pid):

เลือก pg_cancel_backend (procpid);

เพื่อยุติคำขอ ให้ดำเนินการ:

เลือก pg_terminate_backend (procpid);

การทำงานกับการกำหนดค่า

การค้นหาและเปลี่ยนตำแหน่งของอินสแตนซ์คลัสเตอร์

สถานการณ์เป็นไปได้เมื่อมีการกำหนดค่าอินสแตนซ์ PostgreSQL หลายรายการในระบบปฏิบัติการเดียว ซึ่ง "นั่ง" บนพอร์ตต่างๆ ในกรณีนี้ การค้นหาเส้นทางไปยังตำแหน่งทางกายภาพของแต่ละอินสแตนซ์เป็นงานที่ค่อนข้างยุ่งยาก เพื่อให้ได้ข้อมูลนี้ เราจะดำเนินการสืบค้นข้อมูลต่อไปนี้สำหรับฐานข้อมูลใดๆ ของคลัสเตอร์ที่สนใจ:

SHOW data_directory;

มาเปลี่ยนตำแหน่งเป็นอย่างอื่นโดยใช้คำสั่ง:

ตั้งค่า data_directory เป็น new_directory_path;

แต่จำเป็นต้องรีบูตเพื่อให้การเปลี่ยนแปลงมีผล

รับรายการประเภทข้อมูลที่มีอยู่

มาดูรายการประเภทข้อมูลที่ใช้ได้โดยใช้คำสั่ง:

SELECT typname, typlen จาก pg_type โดยที่ typtype = "b";

typname คือชื่อของชนิดข้อมูล
typlen คือขนาดของชนิดข้อมูล

การเปลี่ยนการตั้งค่า DBMS โดยไม่ต้องรีบูต

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

เลือก pg_reload_conf ();

แต่น่าเสียดายที่ใช้ไม่ได้กับพารามิเตอร์ทั้งหมด ในบางกรณี จำเป็นต้องรีบูตเพื่อใช้การตั้งค่า

เราได้กล่าวถึงคำสั่งต่างๆ เพื่อช่วยให้นักพัฒนาและ DBA ใช้ PostgreSQL ได้ง่ายขึ้น แต่นี่ไม่ใช่เทคนิคที่เป็นไปได้ทั้งหมด หากคุณพบปัญหาที่น่าสนใจ เขียนเกี่ยวกับปัญหาเหล่านี้ในความคิดเห็น มาแบ่งปันประสบการณ์ที่มีประโยชน์กันเถอะ!