DEV Community: ใดๆในโลกล้วน Postgres

จัดการข้อมูล Mac Address ให้อยู่หมัดด้วย PostgreSQL

Watchara Sukka — Tue, 24 Jun 2025 18:11:48 +0000

บทนำ

เมื่อต้องจัดการข้อมูลที่เกี่ยวข้องกับระบบเครือข่ายใน PostgreSQL MAC Address จะมีบทบาทสำคัญในการติดตามอุปกรณ์ บันทึกกิจกรรมเครือข่าย และจัดการความปลอดภัย รู้หรือไม่ PostgreSQL มีประเภทข้อมูล macaddr ในตัวเพื่อจัดการ MAC Address อย่างมีประสิทธิภาพ
บทความนี้เราจะอธิบายเกี่ยวกับ:

Mac Address คืออะไร

2.เหตุใดคุณจึงควรใช้ macaddr แทนฟิลด์ข้อความ

3.วิธีจัดเก็บ ค้นหา และจัดรูปแบบที่อยู่ MAC ใน PostgreSQL

4.แนวทางปฏิบัติที่ดีที่สุดสำหรับการสร้างดัชนีและเพิ่มประสิทธิภาพการค้นหา MAC Address

Mac Address คืออะไร

MAC address (Media Access Control Address) คือ หมายเลขประจำเครื่องเฉพาะ ที่ใช้ระบุอุปกรณ์เครือข่าย (Network Interface) เช่น คอมพิวเตอร์, โทรศัพท์มือถือ, เราเตอร์, Wi-Fi card ฯลฯ ในระดับ Layer 2 ของ OSI Model (Data Link Layer) โดยมีรูปแบบ รหัสแบบ 16 ตัวอักษร (6 ไบต์ = 48 บิต) เขียนในรูป เลขฐาน 16 (Hexadecimal) เช่น

00:1A:2B:3C:4D:5E

โดยค่า mac address จะบ่งบอกถึง

ส่วนของ MAC Address	ขนาด	ความหมาย
OUI (Organizationally Unique Identifier)	3 ไบต์แรก	ระบุผู้ผลิต (เช่น Apple, Intel)
NIC Specific	3 ไบต์หลัง	รหัสเฉพาะของการ์ดอุปกรณ์นั้น

เหตุใดคุณจึงควรใช้ macaddr แทนฟิลด์ข้อความ

เราก็จะเกิดคำถามว่าทำไม Postgres ต้องมีการข้อมูลประเภท macaddr สร้างในเมื่อเราสามารถใช้ข้อมูลประเภท text ได้ซึ่งประโยชน์ของข้อมูลประเภท macaddr คือ

ประสิทธิภาพการจัดเก็บ ใช้เพียง 6 ไบต์แทนการแสดงข้อความที่ยาวกว่า
การจัดรูปแบบอัตโนมัติ รับประกันรูปแบบมาตรฐาน (08:00:2b:01:02:03)
การค้นหาที่รวดเร็วยิ่งขึ้น รองรับการสร้างดัชนีที่มีประสิทธิภาพสำหรับการค้นหาอย่างรวดเร็ว
การรองรับการเปรียบเทียบในตัว อนุญาตให้มีการเปรียบเทียบ =, <, > สำหรับการเรียงลำดับและการกรอง

วิธีจัดเก็บ ค้นหา และจัดรูปแบบที่อยู่ MAC ใน PostgreSQL

1.การสรา้งตารางที่มีcolcumn ที่มีข้อมูลประเภท macaddr

CREATE TABLE devices (
    id SERIAL PRIMARY KEY,
    device_name TEXT NOT NULL,
    mac_address MACADDR UNIQUE
);

หมายเหตุต้องมีการกำหนดข้อจำกัด ค่า mac_address เป็น UNIQUE เพื่อป้องกันการเก็บค่า mac address ซ้ำ

2.การเก็บข้อมูล macaddr ดำเนินการผ่านคำสั่ง

INSERT INTO devices (device_name, mac_address) 
VALUES ('Router', '08:00:2b:01:02:03');

หมายเหตุในกรณีที่เราบันทึกค่า mac address ที่ไม่มี colon(08002b010203) เมื่อบันทึกลงฐานข้อมูลจะมีการจัด format ให้ถูกต้อง

3.การเรียกดูข้อมูลผ่านคำสั่ง

SELECT * FROM devices WHERE mac_address = '08:00:2b:01:02:03';

แนวทางปฏิบัติที่ดีที่สุดสำหรับการสร้างดัชนีและเพิ่มประสิทธิภาพการค้นหา MAC Address

เราสามารถเพิ่มประสิทธิธภาพในการเรียกดูข้อมูลประเภท macaddr ผ่านการสรา้ง index

CREATE INDEX mac_index ON devices (mac_address);

การจัดรูปแบบ MAC Address โดยลบเฉพาะของผู้จำหน่ายของที่อยู่ MAC การตัดทอนที่อยู่ MAC (โดยเก็บเฉพาะตัวระบุเฉพาะขององค์กรหรือ OUI):

SELECT macaddr_trunc(mac_address) FROM devices;

จะได้ผลลัพธ์เป็น 08:00:2b:00:00:00

ส่งท้าย

Postgres ได้ให้ประเภทข้อมูล macaddr ซึ่งช่วยให้จัดการข้อมูลเกี่ยวกับการจัดการอุปกรณ์เครือข่ายการใช้งานจริงในการจัดเก็บที่อยู่ MAC ใน PostgreSQL

การรักษาความปลอดภัยเครือข่ายเพื่อติดตามอุปกรณ์ที่เข้าถึงเครือข่าย
การใช้งาน IoT (Internet of Things) เพื่อตรวจสอบอุปกรณ์อัจฉริยะด้วยที่อยู่ MAC เฉพาะ
การจัดการสินค้าคงคลังและสินทรัพย์ ระบุฮาร์ดแวร์ที่เป็นของบริษัท

- ระบบควบคุมการเข้าถึงในการรับรองผู้ใช้ตามที่อยู่ MAC ของอุปกรณ์

🐘 สรุปส่งท้าย

หวังว่าเทคนิคนี้จะช่วยให้การจัดการ Postgres ของคุณง่ายขึ้นนะครับ! หากคุณกำลังมองหาผู้ช่วยที่เชี่ยวชาญเพื่อเข้ามาจัดการระบบข้อมูลให้เสถียรและ Scale ได้จริง...

🚀 ให้ iconnext ดูแลคุณ: รับปรึกษาด้าน Database & Data Architecture
📝 อ่านบทความอื่นในซีรีส์: ใดๆ ในโลกล้วน Postgres
🤝 ทำความรู้จักกันต่อ: LinkedIn | GitHub

"เปลี่ยนเรื่อง Data ที่วุ่นวาย ให้เป็นแต้มต่อด้วยทีมงานมืออาชีพ" — ทีมงาน iconnext

รู้จักกับ plv8: เมื่อ Postgres ต้องมาเชื่อมจิตกับ JavaScript

Watchara Sukka — Tue, 16 Jul 2024 09:41:17 +0000

บทนำ

วันก่อนไปส่อง facebook fanpage ขาเดฟ ซึ่งเป็นแหล่งร่วมตัวเทพ ในวงการ developer ได้โพสเกี่ยวกับส่วนขยาย(extension) ของ postgres ที่ชื่อว่า plv8 ที่ช่วยให้คุณสามารถเขียนฟังก์ชันและทริกเกอร์ด้วยภาษา JavaScript ได้ โดยใช้ V8 JavaScript engine ที่พัฒนาโดย Google

แล้วมันดียังไง

ใช้ JavaScript ในฐานข้อมูล: เขียนโค้ด JavaScript ที่ทำงานโดยตรงใน PostgreSQL
ประสิทธิภาพสูง: V8 engine มีประสิทธิภาพสูง ทำให้การประมวลผลเร็วกว่าภาษาอื่นๆ เช่น PL/pgSQL
ความยืดหยุ่น: สามารถใช้คุณสมบัติของ JavaScript เช่น closures, high-order functions ได้
JSON processing: เหมาะสำหรับการทำงานกับข้อมูล JSON ใน PostgreSQL
NPM packages: สามารถใช้ JavaScript libraries จาก NPM ได้

การใช้งาน

-ติดตั้ง plv8 extension:

CREATE EXTENSION plv8

การสรา้งและเรียกใช้ฟังก์ชั่น:

--สรา้งฟังก์ชั่น hellow_world ในการคืนข้อความ "Hello, World!"
CREATE FUNCTION hello_world() RETURNS text AS $$
    return "Hello, World!";
$$ LANGUAGE plv8;

--การเรียกใช้ function hello_world--
SELECT hello_world();

ฟังก์ชันที่ทำงานกับ JSON และใช้ JavaScript Array methods:

CREATE OR REPLACE FUNCTION filter_and_transform_users(users json) RETURNS json AS $$
    const userArray = JSON.parse(users);
    const result = userArray
        .filter(user => user.age > 18)
        .map(user => ({
            fullName: `${user.firstName} ${user.lastName}`,
            isAdult: user.age >= 21,
            emailDomain: user.email.split('@')[1]
        }));
    return JSON.stringify(result);
$$ LANGUAGE plv8;

-- ตัวอย่างการใช้งาน
SELECT filter_and_transform_users('[
    {"firstName": "John", "lastName": "Doe", "age": 25, "email": "john@example.com"},
    {"firstName": "Jane", "lastName": "Smith", "age": 17, "email": "jane@test.com"},
    {"firstName": "Bob", "lastName": "Johnson", "age": 30, "email": "bob@example.com"}
]');

ฟังก์ชันที่ใช้ closure และ state ระหว่าง invocations:

CREATE OR REPLACE FUNCTION create_counter() RETURNS json AS $$
    const state = plv8.initialize_state();
    if (!state.counter) state.counter = 0;

    function increment() {
        state.counter++;
        return state.counter;
    }

    function decrement() {
        state.counter--;
        return state.counter;
    }

    function reset() {
        state.counter = 0;
        return state.counter;
    }

    return JSON.stringify({increment, decrement, reset});
$$ LANGUAGE plv8;

-- ตัวอย่างการใช้งาน
DO $$
    const counter = JSON.parse(plv8.execute('SELECT create_counter()')[0].create_counter);
    plv8.elog(NOTICE, counter.increment()); // 1
    plv8.elog(NOTICE, counter.increment()); // 2
    plv8.elog(NOTICE, counter.decrement()); // 1
    plv8.elog(NOTICE, counter.reset()); // 0
$$ LANGUAGE plv8;

ฟังก์ชันที่ใช้ external library (ต้องติดตั้ง library ก่อน):

CREATE OR REPLACE FUNCTION validate_email(email text) RETURNS boolean AS $$
    const validator = require('email-validator');
    return validator.validate(email);
$$ LANGUAGE plv8;

-- ตัวอย่างการใช้งาน
SELECT validate_email('user@example.com');
SELECT validate_email('invalid-email');

ทริกเกอร์ที่ใช้ PL/V8:

-- สรา้งตารางเก็บชื่อผู้ใช้งาน 
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT,
    email TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

--สร้างฟังก์ชั่น validate_user() ในการตรวจสอบชื่อผู้ใช้งาน และ email
CREATE OR REPLACE FUNCTION validate_user() RETURNS trigger AS $$
    if (!NEW.username || NEW.username.length < 3) {
        plv8.elog(ERROR, 'Username must be at least 3 characters long');
    }
    if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(NEW.email)) {
        plv8.elog(ERROR, 'Invalid email format');
    }
    return NEW;
$$ LANGUAGE plv8;

--สร้าง trigger ในการเรียกใช้ฟังก์ชั่น validate_user() ในการตรวจสอบชื่อผู้ใช้งาน และ email
CREATE TRIGGER user_validation_trigger
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION validate_user();

-- ตัวอย่างการใช้งาน
INSERT INTO users (username, email) VALUES ('jo', 'john@example.com'); -- จะ error
INSERT INTO users (username, email) VALUES ('john', 'invalid-email'); -- จะ error
INSERT INTO users (username, email) VALUES ('john', 'john@example.com'); -- จะสำเร็จ

มีอะไรต้องระวังไหม

ต้องติดตั้ง PL/V8 extension เพิ่มเติม
การใช้ JavaScript อาจทำให้การ debug ยากขึ้น
ต้องระวังเรื่องความปลอดภัย เพราะ JavaScript สามารถเข้าถึงระบบไฟล์ได้

ส่งท้าย

ส่วนขยาย plv8 ช่วยให้ทาง developer สามารถสร้างฟังก์ชั่นที่ทำงานเฉพาะ ซึ่งฝั่ง developer มีความชำนาญ JavaScript อยู่แล้วโดยไม่ต้องพึ่ง Database Admin (ซึ่งส่วนใหญ่จะไม่ค่อยชำนาญภาษา pl/pgsql) อีกทั้งยังสามารถเรียก npm เพื่อเพิ่มความสามารถผ่าน javscript อย่างที่ผมเคยบอกว่า postgres เป็นทุกอย่างให้เธอแล้ว

🚀 ให้ iconnext ดูแลคุณ: รับปรึกษาด้าน Database & Data Architecture
📝 อ่านบทความอื่นในซีรีส์: ใดๆ ในโลกล้วน Postgres
🤝 ทำความรู้จักกันต่อ: LinkedIn | GitHub ”เปลี่ยนเรื่อง Data ที่วุ่นวาย ให้เป็นแต้มต่อด้วยทีมงานมืออาชีพ" — ทีมงาน iconnext

การแก้ไขปัญหา: ฐานข้อมูล Postgres ถูกใช้งานโดยผู้ใช้อื่น

Watchara Sukka — Sun, 14 Jul 2024 09:47:39 +0000

เมื่อคุณพยายามเข้าถึงหรือลบฐานข้อมูล PostgreSQL และได้รับข้อความแสดงข้อผิดพลาดดังนี้:

ERROR: database "database_name" is being accessed by other users
DETAIL: There are 1 other session(s) using the database.

ข้อผิดพลาดนี้เกิดขึ้นเมื่อมีผู้ใช้หรือโปรแกรมอื่นกำลังเชื่อมต่อกับฐานข้อมูลที่คุณพยายามเข้าถึง ต่อไปนี้คือวิธีแก้ไขปัญหา:

ขั้นตอนที่ 1: ตรวจสอบเซสชันที่ใช้งานฐานข้อมูล

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

SELECT * FROM pg_stat_activity WHERE datname = '<ชื่อฐานข้อมูล>';

แทน <ชื่อฐานข้อมูล> ด้วยชื่อฐานข้อมูลที่คุณกำลังตรวจสอบ
คำสั่งนี้จะแสดงข้อมูลเกี่ยวกับเซสชันทั้งหมดที่กำลังใช้งานฐานข้อมูลนั้น รวมถึง PID (Process ID), ชื่อผู้ใช้, แอปพลิเคชันที่เชื่อมต่อ และอื่น ๆ

ขั้นตอนที่ 2: ยกเลิกการเชื่อมต่อเซสชัน

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

SELECT pg_terminate_backend(pid)
FROM pg_stat_get_activity(NULL::integer)
WHERE datid=(SELECT oid from pg_database where datname = '<ชื่อฐานข้อมูล>');

แทน <ชื่อฐานข้อมูล> ด้วยชื่อฐานข้อมูลที่คุณต้องการยกเลิกการเชื่อมต่อ
คำสั่งนี้จะยกเลิกการเชื่อมต่อทุกเซสชันที่กำลังใช้งานฐานข้อมูลที่ระบุ

ข้อควรระวัง

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

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

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

การใช้คำสั่งเหล่านี้อย่างระมัดระวังจะช่วยให้คุณจัดการกับปัญหาการเข้าถึงฐานข้อมูลที่ถูกใช้งานโดยผู้ใช้อื่นได้อย่างมีประสิทธิภาพ

Database Collation ใน PostgreSQL

Watchara Sukka — Sun, 14 Jul 2024 09:30:35 +0000

Collation คือ

Collation คือชุดกฎที่กำหนดวิธีการเปรียบเทียบและจัดเรียงข้อมูลประเภทข้อความในฐานข้อมูล โดยมีความสำคัญอย่างยิ่งในการจัดการข้อมูลที่เกี่ยวข้องกับภาษาและวัฒนธรรมที่แตกต่างกัน
โดยมีวัตถุประสงค์หลัก 3 ประการ ดังนี้:

1. การเปรียบเทียบข้อมูล: ช่วยให้เปรียบเทียบข้อมูลระหว่างชุดข้อมูลต่างๆ ได้อย่างถูกต้อง แม่นยำ
2. การเรียงลำดับข้อมูล: ช่วยให้เรียงลำดับข้อมูลตามเกณฑ์ต่างๆ เช่น ลำดับตัวอักษร ลำดับตัวเลข หรือวันที่
3. การค้นหาข้อมูล: ช่วยให้ค้นหาข้อมูลที่ต้องการได้รวดเร็วและง่ายดาย

Collation ภาษาไทยใน PostgreSQL

PostgreSQL รองรับ Collation หลากหลายภาษา รวมถึงภาษาไทย โดย Collation ภาษาไทยที่นิยมใช้ ได้แก่

th-TH-unicode: Collation นี้ใช้ Unicode เป็นมาตรฐานในการเปรียบเทียบและเรียงลำดับข้อมูล เหมาะสำหรับการใช้งานทั่วไป
th-TH-TIS620: Collation นี้ใช้มาตรฐาน TIS620 เป็นมาตรฐานในการเปรียบเทียบและเรียงลำดับข้อมูล เหมาะสำหรับการใช้งานกับระบบเก่า
th-TH-dict: Collation นี้ใช้พจนานุกรมไทยเป็นมาตรฐานในการเปรียบเทียบและเรียงลำดับข้อมูล เหมาะสำหรับการใช้งานที่ต้องการเรียงลำดับตามหลักภาษาไทยอย่างเคร่งครัด

การตั้งค่า Collation

ใน Postgres เราสามารถกำหนด Collation ได้ในระดับ Database, Table, Column โดย

ในระดับ Database สามารถกำหนดผ่านคำสั่ง

CREATE DATABASE [ชื่อฐานข้อมูล] COLLATE th-TH-unicode;

ในระดับ Table

CREATE TABLE [ชื่อตาราง] (
  name VARCHAR(255) COLLATE th-TH-unicode
);

ในระดับ Column

ALTER TABLE [ชื่อตาราง]
  ALTER COLUMN [ชื่อคอลัมถ์] SET COLLATE th-TH-unicode;

การใช้งานค้นหาโดยกำหนด

SELECT * FROM mytable ORDER BY name COLLATE th-TH-unicode;

ตัวอย่างการใช้งาน

เพื่อให้เห็นภาพในการใช้งาน Collation ที่เกี่ยวกับการค้นหาจึงขอยกตัวอย่างเป็นภาษาไทยเนื่องจากเป็นบทความภาษาไทย
ตัวอย่างการใช้งาน Collation ภาษาไทยใน PostgreSQL
1.th-TH-x-icu

-- สร้างตารางทดสอบ
CREATE TABLE thai_words (
    word VARCHAR(50) COLLATE "th-TH-x-icu"
);
-- เพิ่มข้อมูลทดสอบ
INSERT INTO thai_words (word) VALUES 
('กข'), ('ขก'), ('คำ'), ('งู'), ('จาน'), ('ฉลาม'), ('ชา'), ('ซุป'), ('ญาติ');
-- ทดสอบการเรียงลำดับ
SELECT * FROM thai_words ORDER BY word;

ผลลัพธ์

word
----
กข
ขก
คำ
งู
จาน
ฉลาม
ชา
ซุป
ญาติ

คำอธิบาย: th-TH-x-icu จะเรียงลำดับตามพจนานุกรมภาษาไทย โดยใช้มาตรฐาน ICU ซึ่งรองรับการเรียงลำดับที่ซับซ้อนของภาษาไทยได้ดี

2.th-TH

-- สร้างตารางทดสอบ
CREATE TABLE thai_names (
    name VARCHAR(50) COLLATE "th-TH"
);
-- เพิ่มข้อมูลทดสอบ
INSERT INTO thai_names (name) VALUES 
('สมชาย'), ('สมหญิง'), ('สมศรี'), ('สมศักดิ์'), ('สมบัติ');
-- ทดสอบการเรียงลำดับ
SELECT * FROM thai_names ORDER BY name;

ผลลัพธ์:

name
----
สมชาย
สมบัติ
สมศรี
สมศักดิ์
สมหญิง

คำอธิบาย: th-TH จะเรียงลำดับตามมาตรฐานทั่วไปของภาษาไทย แต่อาจไม่ละเอียดเท่า th-TH-x-icu ในบางกรณี

3.th_TH.utf8

-- สร้างตารางทดสอบ
CREATE TABLE thai_food (
    food VARCHAR(50) COLLATE "th_TH.utf8"
);
-- เพิ่มข้อมูลทดสอบ
INSERT INTO thai_food (food) VALUES 
('ต้มยำ'), ('ผัดไทย'), ('แกงเขียวหวาน'), ('ส้มตำ'), ('ต้มข่าไก่');
-- ทดสอบการเรียงลำดับ
SELECT * FROM thai_food ORDER BY food;

ผลลัพธ์:

food
----
แกงเขียวหวาน
ต้มข่าไก่
ต้มยำ
ผัดไทย
ส้มตำ

คำอธิบาย: th_TH.utf8 จะเรียงลำดับตามมาตรฐาน UTF-8 ซึ่งรองรับอักขระพิเศษในภาษาไทยได้ดี

4.การเปรียบเทียบระหว่าง Collation

-- สร้างตารางทดสอบ
CREATE TABLE compare_collations (
    word_icu VARCHAR(50) COLLATE "th-TH-x-icu",
    word_th VARCHAR(50) COLLATE "th-TH",
    word_utf8 VARCHAR(50) COLLATE "th_TH.utf8"
);
-- เพิ่มข้อมูลทดสอบ
INSERT INTO compare_collations VALUES 
('เก่ง', 'เก่ง', 'เก่ง'),
('เกง', 'เกง', 'เกง'),
('เก๋ง', 'เก๋ง', 'เก๋ง');
-- ทดสอบการเรียงลำดับด้วย Collation ต่างๆ
SELECT 'ICU' AS collation_type, word_icu AS word FROM compare_collations ORDER BY word_icu
UNION ALL
SELECT 'TH' AS collation_type, word_th AS word FROM compare_collations ORDER BY word_th
UNION ALL
SELECT 'UTF8' AS collation_type, word_utf8 AS word FROM compare_collations ORDER BY word_utf8;

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

การนำเข้าข้อมูลจากไฟล์ CSV เข้ามาใน Posstgres : ทักษะเบื้องต้นของ Data Engineer

Watchara Sukka — Sun, 14 Jul 2024 08:38:10 +0000

บทนำ

เคยไหม ขณะประชุมการขึ้นระบบ CRM ของฝ่ายขายจะต้องมีการนำรายชื่อลูกค้าของแต่ละคนมาใส่ในฐานข้อมูลพอถามว่าเก็บข้อมูลไว้ที่ไหนจะพบว่าบางคนเก็บใน excle, Google Sheet หรือบางคนจดลงในสมุดก็มีซึ่งพนักงานขายบางคนมีรายชื่อตั้งแต่ 100 ถึง 1,000
ปัญหาต่อมาคือใครจะคีย์ข้อมูลเข้าระบบ ในห้องประชุมเงียบ ประธานในที่ประชุมสั่งการให้ IT เขียนโปรแกรมดึงข้อมูลลูกค้าเข้าฐานข้อมูล (ช้ำคือเราเป็น admin ต้องสวมมงเป็น programmer ซะแล้ว)
ถ้าเรารับทำเราก็จะกลายเป็น programer อีกตำแหน่ง(ในเงินเดือนเท่าเดิม) ต้องรองรับทั้ง Excel,Number(สำหรับ Mac) และ Google Sheet ไหนจะต้องให้เสร็จตาม timeline ของระบบ CRM ที่จะขึ้นอีก
หนึ่งในวิธีแก้ปัญหาที่ดีที่สุดให้กับคนที่ชอบโยนปัญหาให้เราคือ... โยนปัญหากลับ เราแจ้งให้กับทางที่ประชุมว่าพี่แปลงข้อมูลพี่เป็นไฟล์ CSV ได้ไหมเดี๋ยวผม import เองเกิดคำถามไปทั้งห้องประชุม ไฟล์ CSV คืออะไร ?

CSV คืออะไรกันแน่? 🤔

CSV ย่อมาจาก "Comma-Separated Values" หรือ "ค่าที่คั่นด้วยเครื่องหมายจุลภาค" ในภาษาไทย แต่อย่าเพิ่งกลัวกับชื่อที่ดูเป็นทางการนี้! ลองนึกภาพว่า CSV เป็นเหมือนตารางข้อมูลแบบง่ายๆ ที่แต่ละช่องถูกแบ่งด้วยเครื่องหมายจุลภาค (,)

ชื่อ,อายุ,อาชีพ
สมชาย,30,วิศวกร
สมหญิง,28,นักการตลาด
สมศรี,35,ครู

เนื่องจากเป็นรูปแบบ text file สามารถอ่านได้ง่ายจึงสามารถ ใช้ text editor ในการป้อนข้อมูลที่จดไว้ในกระดาษมาใส่ในรูปแบบของ CSV ได้

ข้อควรระวังเมื่อใช้ CSV 🚨

ข้อมูลที่มีเครื่องหมายจุลภาค: เช่นตัวเลขจำนวนเงิน เช่น 1,000 ต้องระวังเป็นพิเศษ เพราะอาจทำให้ข้อมูลผิดพลาดได้

การเข้ารหัสตัวอักษร: บางครั้งอาจมีปัญหากับภาษาที่ไม่ใช่ภาษาอังกฤษ

ข้อจำกัดด้านความปลอดภัย: ไม่เหมาะสำหรับข้อมูลที่ต้องการความปลอดภัยสูง(เช่นรหัสผ่าน) เพราะไม่มีการเข้ารหัส

ซึ่งในโปรแกรมจำพวก Excel,Number(สำหรับ Mac) และ Google Sheet มีความสามารถในการ Save as หรือ Export ข้อมูลเป็นรูปแบบ CSV แทบทุกตัว

เมื่อได้ไฟล์ CSV มาแล้วทำยังไงต่อ

เราสามารถนำข้อมูลจากไฟล์ CSV เข้าสุ่ฐานข้อมูลผ่านคำสั่ง

COPY your_table_name(column1, column2, ...)
FROM '/path/to/your/file.csv'
DELIMITER ','
CSV HEADER;

โดย

you_table_name: เป็นตารางที่ต้องการนำข้อมูลจากไฟล์ ไปเก็บ
/path/to/your/file.csv: ตำแหน่งไฟล์ CSV ที่เก็บข้อมูล(ต้องระบุเป็น full path)
คำสั่ง DELIMITER ',': เป็นการบอกให้ใช้เครื่องหมาย , ในการแบ่งข้อมูล
คำสั่ง CSV HEADER : เป็นการบอกให้ดึงข้อมูลแบบ CSV โดยบอกให้รู้ว่าแถวแรกของไฟล์เป็นชื่อคอลัมน์ ไม่ใช่ข้อมูลจริง

ข้อควรรู้
การนำข้อมูลเข้านอกจากคำสั่งCOPY แล้วยังมี \COPY โดยความแตกต่างกันคือ COPY จะดำเนินการบน server ในขณะที่ \COPY ดำเนินการบน client

บทส่งท้าย

การนำเข้าข้อมูลงฐานข้อมูลเห็นหน้าที่หนึ่งในฐานะ database administrator พึ่งกระทำไม่ใช่หน้าที่ในการเขียนโปรแกรมในการนำเข้าข้อมูล ตัว Postres มีเครื่องมือในการนำเข้าข้อมูลสู่ตารางฐานข้อมูล นำเข้าแล้วอย่าลืมตรวจสอบข้อมูลหลังนำเข้าด้วยล่ะ
จริงแล้วการนำข้อมูลเข้า หรือส่งออกจากฐานข้อมูลถือเป็นทักษธหนึ่งที่ Data Engineer พึงมี
ขอให้มีความสุขกับ ใดๆ โลกล้วน Postgres ด้วยนะครับ

คู่มือเอาตัวรอด :การใช้งาน PostgreSQL ผ่าน command line

Watchara Sukka — Fri, 12 Jul 2024 09:48:14 +0000

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

เปิดจาก Application ไม่สามารถเชื่อมต่อ database ได้
ติดตั้ง Database Management Application พวก pgAdmin , DBeaver, Navicat etc. ปรากฏว่า ขึ้นไม่สามารถเชื่อมต่อฐานข้อมูลได้
ping ดูว่า server ไหม? ไม่ล่มแล้วไป เลยทดสอบ telnet (โบราณไป..) ssh ไปยัง server พบแต่หน้าจอดำๆ กับพิมพ์ได้อย่างเดียว แล้วไงต่อ การใช้ command line คือทางออก

psql คืออะไร

เป็นโปรแกรมสำหรับโต้ตอบกับฐานข้อมูล PostgreSQL โดยใช้คำสั่ง SQL

การใช้งาน

เข้าใช้งานด้วยคำสั่ง

$psql -U <username> -d <database> -W

เมื่อ

username: ชื่อผู้ใช้งานใน postgres
database: ชื่อฐานข้อมูลที่ต้องการเชื่อมต่อ

Tips พารามิเตอร์ที่เกี่ยวกับการเชื่อมต่อ
-h, --host=HOSTNAME: database server host or socket directory (ค่าเริ่มต้น: "local socket")
-d, --dbname=DBNAME: ฐานข้อมูลที่ต้องการเชื่อม(default: "root")
-p, --port=PORT:port ที่ใช้ในการเชื่อมต่อ (ค่าเริ่มต้น: "5432")
-U, --username=USERNAME: ชื่อผู้ใช้งาน (ค่าเริ่มต้น: "root")
-w, --no-password:ไม่ให้ขึ้น prompt ในการป้อนรหัสผ่าน
-W, --password: กำหนดให้ขึ้น prompt ในการป้อนรหัสผ่าน(ควรเกิดขึ้นอัติโนมัติ)
เมื่อเข้าสำเร็จจะแสดง prompt ของ postgres

postgres=#

ที่ postgres prompt ให้พิมพ์คำสั่ง

\l ดู database ทั้งหมด

\c <ชื่อฐานข้อมูล> เข้าใช้งานฐานข้อมูลที่ต้องการ

จะเปลี่ยนเป็น prompt เป็นชื่อฐานข้อมูลนั้น โดยเมื่ออยู่ใน prompt ของฐานข้อมูลแล้วเราสามารถใช้คำสั่ง SQL ในการจัดการข้อมูลในฐานข้อมูลนั้นได้ปรกติ

\dt ดู table ทั้งหมดใน database ปัจจุบัน

\dt+ ดู size และ description ของ table เพิ่มเติม

\d <ชื่อตาราง> ดูโครงสร้างของตารางตามที่กำหนด

\dv ดู view ทั้งหมด
\df ดู function ทั้งหมด

\dx ดู extension ที่ติดตั้งทั้งหมด

\du ดู user ทั้งหมด

\e ทำการเปิด editor เพื่อแก้ไข command(ต้องมีการกำหนดค่า$EDITOR environment variable)
\s <ชื่อไฟล์> บันทึก comand ลง file ตามชื่อที่กำหนด
! clear สำหรับ clear หน้าจอ
\h => Help
\? ดูว่ามี command อะไรให้ใช้งานบ้าง
\q ออก เลิกใช้งาน …

หมายเหตุ
กรณีที่เราใช้ \c ในการเข้า prompt ของฐานข้อมูลคำสั่งต่างๆ จะแสดงรายการเฉพาะฐานข้อมูลที่เลือก ในกรณีที่ยังไม่กำหนดฐานข้อมูลจะแสดงข้อมูลรายการของทั้ง postgres server

แถมท้าย
เราสามารถดึงข้อมูลพร้อมexport เป็นไฟล์ CSV ให้หัวหน้า HR นำไปใช้งานต่อ(เย้..เลิกงานพอดีและวันนี้ต้องมีคนทำ otและมีตรได้ฉลองเงินเดือนออก)

COPY (SELECT * FROM foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;

บทส่งท้าย
command line เป็นเครื่องมือในการจัดการฐานข้อมูลซึ่งไม่ได้มีเฉพาะใน postgres เท่านั้นฐานข้อมูลอื่นๆ ส่วนมี command line ในการบริหารจัดการฐานข้อมูลได้การที่เราสามารถใช้งานได้จะช่วยเพิ่มทางเลือกในการแก้ปัญหามากขึ้น นอกจากนี้เวลาผู้ใช้เห็นเราใช้ command line แก้ปัญหาจะทำให้ดูหล่อ ประดุจ hacker ในหนังแนวอาชญากรรมทางไซเบอร์ฏ็ไม่ปาน

ข้อมูล XML บน Postgres : มารู้จักรุ่นพี่ของวงการแลกเปลี่ยนข้อมูลระหว่าง service กัน

Watchara Sukka — Fri, 12 Jul 2024 06:51:50 +0000

บทนำ

ครั้งก่อนเราเคยทำบทความ "ข้อมูล JSON ใน PostgreSQL: โลกใหม่ของการจัดการข้อมูลบน Postgres" ซึ่งเกี่ยวกับการจัดการข้อมูล JSON ไปแล้ววันนี้เราจะมาคุยเรื่องการจัดการข้อมูลแบบ xml กัน
ในยุคสมัยที่มีการแลกเลี่ยนข้อมูลระหว่าง service ผ่าน API โดยส่งข้อมูลในรูปแบบ JSON เคยทราบไหมว่าสมัยก่อน(สมัยนี้บางระบบก็ยังมีอยู่) เคยมีรูปแบบการแลกเปลี่ยนข้อมูลระหว่าง server โดยโปรโตคอลที่เรียกว่า SOAP(Simple Object Access Protocol) ซึ่งเป็นการแลกเปลี่ยนข้อมูลในรูปแบบ XML

แล้ว XML คืออะไรล่ะ

XML(Extensible Markup Language)ก็คือภาษาที่ใช้กำหนดโครงสร้างของข้อมูลแบบข้อความคล้ายกับภาษา HTML ซึ่งก็คือภาษากำหนดโครงสรา้งที่แสดงหน้าเว็บยังไงล่ะโดยรูปแบบของ XML จะประกอบด้วย

โครงสร้างแบบต้นไม้: ข้อมูล XML จะถูกจัดเรียงเป็นโครงสร้างแบบต้นไม้ ประกอบด้วย element attribute และ content
Element: หน่วยพื้นฐานของ XML เปรียบเสมือนแท็กใน HTML แต่ละ element จะมีชื่อและอาจมี attribute เพิ่มเติม
Attribute: ข้อมูลเพิ่มเติมเกี่ยวกับ element เก็บไว้ในรูปคู่ key-value
Content: เนื้อหาที่อยู่ภายใน element อาจเป็นข้อความ element ย่อย หรือ entity references
Document: โครงสร้าง XML ทั้งหมด ประกอบด้วย element attribute และ content ตามตัวอย่าง

<?xml version="1.0" encoding="UTF-8"?>
<product>
  <name>สินค้า A</name>
  <price>100</price>
  <stock>50</stock>
  <categories>
    <category>หมวดหมู่ 1</category>
    <category>หมวดหมู่ 2</category>
  </categories>
</product>

แล้ว Postgres จัดการข้อมูลชนิด XML อย่างไร
ตัว postgres ไม่มีประเภทข้อมูล xml โดย postgres จะดำเนินการผ่าน extension ที่ชื่อ xml2 ซึ่งช่วยให้สามารถจัดเก็บ ประมวลผล และดึงข้อมูล XML ในฐานข้อมูล PostgreSQL ได้อย่างมีประสิทธิภาพ โดยรองรับการจัดการประเภทข้อมูลคือ

xml: เก็บข้อมูล XML แบบดั้งเดิม(text)
xmlb: เก็บข้อมูล XML แบบ binary (เอะคุ้นๆ นะ ดังนั้นข้อดีของ xmlb น่าจะคล้ายกับประเภทข้อมูล json)

การสร้างคอลัมน์ประเภท XML

CREATE TABLE products (
  id serial PRIMARY KEY,
  name varchar(255) NOT NULL,
  details xml NOT NULL
);

จะเป็นการสร้างตารางที่มีคอลัมน์ details มีข้อมูลชนิด xml ขึ้นมา

การเพิ่มข้อมูล XML

INSERT INTO products (name, details)
VALUES ('สินค้า A', '<product><name>สินค้า A</name><price>100</price><stock>50</stock></product>');
Use code with caution.
content_copy

การดึงข้อมูล XML

SELECT id, name, details->'name' AS product_name FROM products;
Use code with caution.
content_copy

การแก้ไขข้อมูล XML

UPDATE products
SET details = details || xml '<discount>10</discount>'
WHERE id = 1;
Use code with caution.

ฟังก์ชันสำหรับการประมวลผลข้อมูล XML

PostgreSQL รองรับฟังก์ชัน XML มากมาย
สำหรับการประมวลผลข้อมูล XML ตัวอย่างฟังก์ชันที่ใช้ทั่วไป ได้แก่:

xml_parse: แปลงสตริง XML เป็นค่าประเภท XML
xml_serialize: แปลงค่าประเภท XML เป็นสตริง XML
xpath: ดึงข้อมูล XML โดยใช้ XPath

ส่งท้าย
ถามว่าเรายังจำเป็นต้องเก็บข้อมูล XML อยู่ไหมผมเคยพัฒนาระบบที่ต้องส่งข้อมูลไปยัง service ที่ใช้ SOAP โปรโตคอลในการเชื่อมต่อซึ่งต้องส่งและเก็บข้อมูลการทำ transaction ย้อนหลังได้ตอนนั้นใช้ Microsoft SQL Server ที่มีประเภทข้อมูลเป็น xml ติดมาด้วย อีกทั้งยังมีอีกหลาย service ที่ยังมีการใช้ SOAP หวังว่าบทความนี้จะเป็นประโยชน์บ้างนะครับ
xml_update: อัปเดตข้อมูล XML

ข้อมูล JSON ใน PostgreSQL: โลกใหม่ของการจัดการข้อมูลบน Postgres

Watchara Sukka — Fri, 12 Jul 2024 03:22:09 +0000

เกรินนำ

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

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

รูปแบบของ JSON

วัตถุ (Object)
วัตถุใน JSON ประกอบด้วยคู่ชื่อ-ค่า (name-value pairs) โดยใช้เครื่องหมาย {} และเครื่องหมายจุลภาค , เพื่อคั่นคู่ชื่อ-ค่าแต่ละคู่:

{
    "name": "Alice",
    "age": 25,
    "city": "Wonderland"
}

อาร์เรย์ (Array)

อาร์เรย์ใน JSON ประกอบด้วยรายการของค่าโดยใช้เครื่องหมาย [] และเครื่องหมายจุลภาค , เพื่อคั่นค่าต่าง ๆ:

[
    "Apple",
    "Banana",
    "Cherry"
]

ประเภทของค่าใน JSON

สตริง (String): ข้อความที่อยู่ในเครื่องหมายอัญประกาศคู่ ""

"example": "Hello, World!"

ตัวเลข (Number): ค่าตัวเลข

"example": 123

วัตถุ (Object): ชุดของคู่ชื่อ-ค่า

"example": {"key": "value"}

อาร์เรย์ (Array): รายการของค่า

"example": ["item1", "item2"]

บูลีน (Boolean): ค่าความจริง true หรือ false

"example": true

ค่าว่าง (Null): ค่าว่าง null

"example": null

ตัวอย่างที่ซับซ้อนขึ้น
แสดง Array ของ user Object ซึ่งObject user ประกอบด้วยชื่อ(name)และเมือง(city)เป็น String และอายุ(age) เป็น Number

{
    "users": [
        {
            "name": "Alice",
            "age": 25,
            "city": "Wonderland"
        },
        {
            "name": "Bob",
            "age": 30,
            "city": "Builderland"
        }
    ],
    "isActive": true,
    "totalUsers": 2
}

มาเริ่มจัดการข้อมูล JSON กัน

สรา้งตารางที่มี column เป็น JSON
เริ่มต้นสร้างตารางขึ้นมาก่อน

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    data JSONB
);

สร้างตาราง users ที่มีคอลัมน์ data เป็น JSONB

การแทรกข้อมูล JSON
การแทรกข้อมูล JSON ก็ง่ายไม่แพ้กัน ลองดูตัวอย่างนี้:

INSERT INTO users (data) VALUES ('{"name": "Alice", "age": 30, "city": "Wonderland"}');

ง่ายเหมือนการใส่ข้อมูลลงในฐานข้อมูลแบบธรรมดา!

การค้นหาข้อมูล JSON
การค้นหาข้อมูล JSON ใน PostgreSQL นั้นเหมือนการค้นหาในโลกแห่งมายา คุณสามารถใช้คำสั่งนี้:

Copy code
SELECT data->>'name' AS name FROM users WHERE data->>'city' = 'Wonderland';

คำสั่งนี้จะค้นหาผู้ใช้ที่อาศัยอยู่ในเมือง Wonderland และแสดงชื่อของพวกเขา

การอัปเดตข้อมูล JSON
การอัปเดตข้อมูล JSON ก็ทำได้ง่ายและรวดเร็ว ลองดูตัวอย่างนี้:

UPDATE users SET data = jsonb_set(data, '{age}', '31') WHERE data->>'name' = 'Alice';

เพียงเท่านี้ อายุของ Alice ก็จะถูกอัปเดตเป็น 31 ปี!

การลบข้อมูล JSON
การลบข้อมูล JSON ทำได้โดยการระบุคีย์ที่ต้องการลบ:

Copy code
UPDATE users SET data = data - 'city' WHERE data->>'name' = 'Alice';

คำสั่งนี้จะลบข้อมูลเมืองของ Alice ออกไป

ฟังก์ชั่น และตัวดำเนินการเกี่ยวกับข้อมูล JSON
จากการหัวข้อที่ผ่านมาจะพบว่า syntax หลายส่วนจะไม่ตรงตามรูปแบบภาษา SQL นักเนื่องจากประเภทข้อมูล JSON ไม่ใช่ชนิดข้อมูลมาตรฐานของ SQL แต่ Postgres ก็มีเครื่องมือที่ทรงพลังในการจัดการข้อมูลอยู่
***ตัวดำเนินการในการดึงข้อมูล

-> (Extract JSON Object Field): ดึงค่า JSON object field โดยไม่แปลงเป็นประเภทอื่น
->> (Extract JSON Object Field as Text): ดึงค่า JSON object field และแปลงเป็นข้อความ
#> (Extract JSON Sub-Object): ดึงค่า JSON sub-object
#>> (Extract JSON Sub-Object as Text): ดึงค่า JSON sub-object และแปลงเป็นข้อความ ฟังก์ชันสำหรับการอัปเดตข้อมูล JSON
jsonb_set: อัปเดตค่าใน JSONB
jsonb_insert: แทรกค่าใหม่ใน JSONB
jsonb_delete: ลบคีย์ออกจาก JSONB ฟังก์ชันสำหรับการสร้างและการตรวจสอบ JSON
json_build_object: สร้าง JSON object จากคู่คีย์-ค่า
jsonb_pretty: จัดรูปแบบ JSONB ให้สวยงาม
jsonb_typeof: ตรวจสอบประเภทของค่าใน JSONB

หมายเหตุ
สามารถดูรายละเอียดเพิ่มเติม
https://www.postgresql.org/docs/current/functions-json.html

ข้อควรรู้

- JSON กับ JSONB ไม่เหมือนกันนะ JSON กับจะเก็บข้อมูลในรูปแบบ text ในขณะที่ JSONB จะเก็บในรูปแบบ binary ทำให้รองรับการบีบอัดเพื่อประหยัดพื้นที่ และการเข้าข้อมูลแบบสุ่มทำให้เร็วกว่าจึงเหมาะสำหรับการเก็บข้อมูล JSON ที่ต้องการดัดแปลงบ่อย
- การทำ Index ในข้อมูลชนิด JSON แนะนำดัชนีเป็นประเภท B-Tree ซึ่งทำงานได้ดีกว่า
- ขนาดข้อมูลสูงสุดที่รับได้ ใน postgres กำหนดค่าเริ่มต้นขนาดเพจไว้ 8KB และกำหนดประเภทข้อมูล JSON ไว้ 1 เพจดังนั้นขนาดข้อมูลจึงไม่ควรเกิน 8 KB

ส่งท้าย
หวังว่าบนความนี้จะทำให้เข้าใจข้อมูลประเภท JSON และสนุกกับการจัดการข้อมูลประเภทนี้ผ่าน postgres มากขึ้นเพื่อนำมาใช้ในproject ต่างๆได้ครับแล้วพบกันใหม่ครับ

การจัดการ Enum ใน PostgreSQL: เพื่อประสิทธิภาพและความชัดเจนให้ฐานข้อมูลของคุณ

Watchara Sukka — Mon, 08 Jul 2024 06:48:16 +0000

Enum คือ

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

วันในสัปดาห์: จันทร์, อังคาร, พุธ, ...
สถานะการส่งสินค้า: กำลังแพ็คของ, กำลังจัดส่ง, จัดส่งสำเร็จ
ระดับความสำคัญของงาน: ต่ำ, ปานกลาง, สูง

การจัดการ Enum

1.การสร้างประเภทข้อมูล Enum

CREATE TYPE mood AS ENUM ('happy', 'neutral', 'sad');

คำสั่งนี้สร้าง Enum ชื่อ 'mood' ที่มีค่าเป็นไปได้ 3 ค่า

โดยสามารถตรวจสอบ type ที่สรา้งขึ้นผ่านคำสั่ง

SELECT n.nspname as enum_schema,  
       t.typname as enum_name,  
       e.enumlabel as enum_value
FROM pg_type t 
   JOIN pg_enum e ON t.oid = e.enumtypid  
   JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace;

จะแสดงรายการ

2.การนำ Enum ไปใช้ในตารางข้อมูล

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

CREATE TABLE daily_log (
    id SERIAL PRIMARY KEY,
    date DATE NOT NULL,
    user_mood mood NOT NULL DEFAULT 'happy'
);

เป็นการสรา้งตาราง daily_log โดยคอลัมน์ user_mood ซึ่งเป็นประเภทข้อมูล mood Enum ที่สร้างไว้ก่อนหน้า

3.การเพิ่มข้อมูลโดยใช้ Enum

INSERT INTO daily_log (date, user_mood) VALUES 
('2024-07-12', 'happy'),
('2024-07-13', 'neutral'),
('2024-07-14', 'sad');

4.การค้นหาข้อมูลโดยใช้ Enum

-- หาบันทึกทั้งหมดที่ผู้ใช้มีความรู้สึกมีความสุข
SELECT * FROM daily_log WHERE user_mood = 'happy';

-- หาบันทึกที่ผู้ใช้ไม่มีความสุข
SELECT * FROM daily_log WHERE user_mood != 'happy';

5.การอัปเดตข้อมูลใน Enum

การอัปเดตข้อมูลในคอลัมน์ Enum เช่นเดียวกับคอลัมน์ประเภทข้อมูลอื่นๆ

UPDATE daily_log
SET mood = 'happy'
WHERE id = 3;

ตัวอย่างข้างต้นเปลี่ยนแปลง user_mood ในตาราง dialy_log ที่มีค่า id 3 เป็น happy

6.การลบ Enum

การลบ Enum ได้ด้วยคำสั่ง DROP TYPE

DROP TYPE mood;

อย่างไรก็ตาม โปรดทราบว่าการลบ Enum จะลบการอ้างอิงทั้งหมดในตารางออกด้วย

เทคนิคการใช้ Enum

การตรวจสอบ

-- ตรวจสอบว่าค่าอยู่ใน Enum หรือไม่
SELECT 'happy'::mood;  -- ทำงานได้
SELECT 'excited'::mood;  -- เกิด error

-- ดึงรายการค่าทั้งหมดของ Enum
SELECT enum_range(NULL::mood);

การเปรียบเทียบและเรียงลำดับใน Eum

การเรียงลำดับค่า Enum ตามที่ประกาศไว้ เช่นเมื่อเราประกาศ type enum

--จากenum mood ที่สร้างขึ้นค่า 'happy' จะถูกเรียงลำดับก่อน 'neutral'
--และ 'neutral'จะถูกเรียงลำดับก่อน'sad'

-- หาบันทึกที่มีความรู้สึกดีกว่า 'neutral'
SELECT * FROM daily_log WHERE user_mood > 'neutral';

-- หาบันทึกที่มีความรู้สึกที่ไม่ใช่ 'sad'
SELECT * FROM daily_log WHERE user_mood <> 'sad';

--เรียงบันทึกความรู้สึกจาก  happy ไปถึง sad
SELECT * FROM daily_log ORDER BY user_mood;

ฟังก์ชัน Enum ในตัว

ใน Postgres มีฟังก์ชันตัวในการจัดการกับ Enum ฟังก์ชันเหล่านี้ช่วยให้คุณสามารถตรวจสอบว่าค่าอยู่ใน Enum หรือไม่ แปลงค่าเป็นตัวอักษร หรือดึงรายการค่าที่เป็นไปได้ทั้งหมด

- has_any_status(status_array, status): ตรวจสอบว่าค่าใดๆ ใน status_array อยู่ใน status หรือไม่
-has_status(status, value): ตรวจสอบว่า value อยู่ใน status หรือไม่
- enum_label(status, value): แปลง value เป็นตัวอักษร
- enum_range(status): ดึงรายการค่าที่เป็นไปได้ทั้งหมดของ status

บทสรุป: Enum - เครื่องมือทรงพลังสำหรับฐานข้อมูลของคุณ

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

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

PostgreSQL Extension: ขยายศักยภาพฐานข้อมูลของคุณ

Watchara Sukka — Thu, 27 Jun 2024 02:58:34 +0000

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

ชนิดข้อมูลเพิ่มเติม: โมดูลเสริมสามารถนำเสนอชนิดข้อมูลใหม่ ๆ ที่ออกแบบมาโดยเฉพาะสำหรับข้อมูลประเภทเฉพาะ เช่น ข้อมูลทางภูมิศาสตร์ หรือ เอกสาร JSON
ฟังก์ชันและตัวดำเนินการ: โมดูลเสริมสามารถจัดเตรียมฟังก์ชันและตัวดำเนินการใหม่ ๆ ที่สามารถใช้ในคิวรี SQL เพื่อดำเนินงานเฉพาะทาง
ฟีเจอร์ใหม่: โมดูลเสริมสามารถนำเสนอฟีเจอร์ใหม่ ๆ เพิ่มเติมให้กับ PostgreSQL โดยสิ้นเชิง เช่น การรองรับการค้นหาแบบเต็มรูปแบบ หรือ การเข้ารหัส

ข้อดีของการใช้โมดูลเสริม PostgreSQL

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

สิ่งที่ควรคำนึงถึงเมื่อใช้โมดูลเสริม PostgreSQL:

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

การจัดการ Extension

ในการตรวจสอบ extension ที่อยู่ใน PostgreSQL

สามารถตรวจสอบผ่านคำสั่ง SQL

SELECT * FROM pg_available_extensions;

จะเป็น view ในการแสดงรายการ extension ที่พร้อมใช้งาน ใน server ของเราโดยจะแสดงข้อมูล

name: ชื่อ extension
default_version:
install_version: เวอร์ชั่นที่ทำการติดตั้งอยู่ในฐานข้อมูล
comment: คำอธิบาย extension

การตรวจสอบ Extension ที่ติดตั้งบนฐานข้อมูล

สามารถทำผ่านคำสั่ง SQL

SELECT * FROM pg_extension;

จะแสดงรายการที่ extension ติดตั้งในฐานข้อมูล

โดยแสดงข้อมูล

oid: object id ของ extension
extname:ชื่อ extension
extowner: id owner ของ object
extnamespace
extrelocatable
extversion: version ของ extension
extconfig:
extcondition:

ในการติดตั้งเข้าไปในฐานข้อมูลผ่านคำสั่ง

CREATE EXTENSION {extension_name};

เมื่อ extension_name เป็นชื่อที่ต้องการติดตั้ง

ในการถอดถอน extension ออกจากฐานจ้อมูลผ่านคำสั่ง

คำเตือน
1.คุณต้องมีสิทธิ์ CREATE EXTENSION ในฐานข้อมูลที่คุณต้องการติดตั้ง extension

ตรวจสอบให้แน่ใจว่าคุณดาวน์โหลด extension ที่ถูกต้องสำหรับเวอร์ชัน PostgreSQL ของคุณ

บาง extension อาจมีข้อกำหนดเบื้องต้นเพิ่มเติม โปรดอ่านเอกสารประกอบของ extension สำหรับข้อมูลเพิ่มเติม

DROP EXTENSION {extension_name};

เมื่อ extension_name เป็นชื่อที่ต้องการติดตั้ง

คำเตือน

คุณต้องมีสิทธิ์ DROP EXTENSION ในฐานข้อมูลที่คุณต้องการถอดถอน Extension

ตรวจสอบให้แน่ใจว่าคุณได้ลบวัตถุใดๆ ที่สร้างโดย Extension ออกก่อน มิฉะนั้น คุณอาจได้รับข้อความแสดงข้อผิดพลาด

บาง Extension อาจมีขั้นตอนการถอนการติดตั้งเพิ่มเติม โปรดอ่านเอกสารประกอบของ Extension สำหรับข้อมูลเพิ่มเติม

กรณีที่ต้องการติดตั้ง Extension ที่ไม่ปรากฏในรายการที่อยู่ Postgres

ในบาง extension ไม่ปรากฏผ่าน pg_available_extensions ให้ไม่สามารถติดตั้ง extension บนฐานข้อมูลได้ถ้าต้องการติดตั้ง

ดาวน์โหลดซอร์สโค้ด Extension: คุณสามารถดาวน์โหลดซอร์สโค้ด Extension จากเว็บไซต์ของผู้พัฒนา
คอมไพล์ Extension: ขั้นตอนการคอมไพล์ Extension แตกต่างกันไปขึ้นอยู่กับ Extensionโปรดดูเอกสารประกอบของ Extension สำหรับข้อมูลเพิ่มเติม
ติดตั้ง Extension: หลังจากคอมไพล์ Extension แล้ว คุณสามารถติดตั้ง Extension ได้โดยใช้คำสั่ง SQL CREATE EXTENSION

ข้อควรระวัง:

ติดตั้ง Extension จากแหล่งที่เชื่อถือได้เท่านั้น

ตรวจสอบให้แน่ใจว่า Extension เข้ากันได้กับเวอร์ชัน PostgreSQL ของคุณ

บาง Extension อาจมีข้อกำหนดเบื้องต้นเพิ่มเติม โปรดอ่านเอกสารประกอบของ Extension สำหรับข้อมูลเพิ่มเติม

ก่อนติดตั้ง Extension ใดๆ โปรดสำรองฐานข้อมูลของคุณไว้ก่อน

ตรวจสอบให้แน่ใจว่าคุณเข้าใจผลกระทบของ Extension ที่คุณกำลังติดตั้ง

บาง Extension อาจส่งผลต่อประสิทธิภาพการทำงานของ PostgreSQL ของคุณ

Postgres ต้องรอด!: วิธีกู้คืนรหัสผ่านผู้ใช้ postgres เมื่อลืมแบบฉุกเฉิน

Watchara Sukka — Sat, 22 Jun 2024 09:17:12 +0000

เกริ่นนำ

หนึ่งในหน้าที่ของ database adminที่ต้องเผชิญคือการรับมรดกบาปจาก admin คนเก่า, vendor หรือแม้กระทั้งตัวเอง
หัวหน้าฝ่ายการตลาดต้องการสรุป สรุป ข้อมูลการขายของไตรมาสนี้บนระบบ erp ขณะที่เราใช้ pgAdmin หลังจากใช่ username และ password คลิกปุ่ม Login หน้าจอแสดง "username/password is not valid" ว่าแต่ password คืออะไรหว่า ? สิ่งที่ปรกติแล้วในกรณีหาแล้วไม่เจอคือ

โทรหา IT คนเก่าคำตอบคือ จำไม่ได้หรอกลองถาม vendor ดูไหมเผื่อจำได้(ไปข้อ 2.)
โทรถาม vender คำตอบคือผมให้ข้อมูล IT คนเก่าไปหมดแล้ว(ไปข้อ 3)
ลองหาตามไฟล์ หรือสมุดโน้ต(ถ้าจำได้ หรือเจอจะไปข้อ 1 ทำไม)

ดูทรงน่าจะเริ่มติดลูป แหละ reset password user postgres เลยละกันหัวหน้าตามแหละว่าแต่ user ที่ชื่อ postgres เป็นใคร?

ผู้ใช้งาน postres เป็นใคร

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

สร้างฐานข้อมูลใหม่
จัดการสิทธิ์ผู้ใช้
ปรับแต่งการตั้งค่าระบบ

การ reset password ของผู้ใช้งาน postgres

อย่างแรกไปยังหน้า command line ของระบบปฏิบัติการนั้นจะเดินไปหน้าเครื่องก็ได้จะรีโมทโดย ssh ก็ได้(อย่าใช้ telnet เชียวโดน IT manager ล้อตาย)
โดยในการ reset password จะดำเนินการผ่านไฟล์ pg_hba.conf ซึ่งจะอยู่ที่ตำแหน่ง /var/lib/postgresql/data บน linux หรือ C:\Program Files\PostgreSQL\16\data บน Windows โดยไฟล์ดังกล่าวมีหน้าที่ในการควบคุมการ authentication จาก client ที่เชื่อมต่อเข้ามาโดย hba ย่อมาจาก host base authentication

1.ทำการสำรองไฟล์ pg_hba.conf

# cp pg_hba.conf pg_hba.conf.bak

เกร็ดความรู้: pg_hba.conf ย่อมาจาก "PostgreSQL Host-Based >Authentication Configuration" ไฟล์นี้ควบคุมวิธีการที่ PostgreSQL ตรวจสอบและอนุญาตการเชื่อมต่อจากไคลเอนต์

2.เแก้ไขไฟล์ pg_hba.conf
เปิดไฟล์ด้วย text editor ที่คุณถนัด เช่น nano หรือ vim จากนั้นไปบริเวณท้ายไฟล์ในบรรทัดที่เป็น host all all all scram-sha-256 ให้เปลี่ยนคำว่า scram-sha-256 เป็น trust จากนั้นทำการบันทึกไฟล์

คำเตือน: การเปลี่ยนเป็น "trust" จะอนุญาตการเชื่อมต่อโดยไม่ต้องใช้รหัสผ่าน ใช้เฉพาะในกรณีฉุกเฉินและอย่าลืมเปลี่ยนกลับ!

3.ทำการ restart service PostgreSQL

# pg_ctl restart

4.ทำการเข้า PostgreSQL อีกครั้งผ่านคำสั่ง

# psql -U postgres

5.จะเข้ามายัง shell ของ PostgreSQL โดยไม่ต้องป้อนรหัสผ่านจากนั้นให้ทำการเปลี่ยนรหัสผ่านผ่านคำสั่ง

postgres=# ALTER USER postgres WITH PASSWORD 'new_password';

เมื่อ new_password เป็นรหัสผ่านใหม่
6.นำค่า pg_hba.conf ที่สำรองไว้แทน pg_hba.conf ที่แก้ค่าไปจากนั้นทำการrestart PostgreSQL service แล้วทดสอบเข้า psql โดยใช้ รหัสผ่านที่ตั้งใหม่

ส่งท้าย

ปัญหาการลืมรหัสผ่านเป็น ปัญหาคลาสสิคของ database admin หวังว่าบทความนี้จะเป็นดั่งยาสามัญประจำบ้าน ช่วยให้เรารอดจากวิกฤติได้

การสร้างผู้ใช้และให้สิทธิความเป็นเจ้าของฐานข้อมูล PostgreSQL บน Ubuntu

Watchara Sukka — Sat, 22 Jun 2024 02:39:23 +0000

บทนำ

ในฐานะ database administrator เราต้องมีการสรา้งฐานข้อมูลให้ ทีม developer ในการบริหารจัดการระบบที่จะพัฒนาขึ้น ในการสรา้งฐานข้อมูล postgres ซึ่งจะเป็นเจ้าของฐานข้อมูลทุกตัวที่สร้างขึ้นมา จะให้รหัสผ่าน postgres กับทีม developor กระดากใจ จะสร้างผู้ใช้งานและกำหนดสิทธิ ก็เดี๋ยวต้องมาคอยกำหนดสิทธิเพิ่มเติมอีกให้ว้าวุ่นกันอีก(เพราะในกระบวนพัฒนาระบบโครงสร้างข้อมูลยังไม่นิ่ง) ดังนั้นวิธีดีคือ เราจึงต้องสร้างผู้ใช้งาน และมอบสิทธิความเป็นเจ้าของ(owner)ให้ฐานข้อมูลนั้นเพื่อให้สามารถเข้าถึงฐานข้อมูลนั้น และในกรณีที่รหัสผ่านของผู้ใช้งานนั้นหลุดก็มีผลกระทบแค่กับฐานข้อมูลนั้น

บทความนี้จะแสดงการสร้างผู้ใช้งาน และให้สิทธิความเป็นเจ้าของฐานข้อมูล PosgreSQL ผ่าน command line บน Ubuntu Linux

วิธีดังกล่าวสามารถนำไปใช้กับ PostgreSQL ที่ติดตั้งในระบบปฏิบัติอื่นๆได้

การสร้างผู้ใช้งานใหม่บน PostgreSQL

1.เข้าใช้งาน psql เพื่อเข้าใช้งาน PostGreSQL ผ่าน command line ที่ terminal ใน Ubuntu พิมพ์

# sudo -u postgres psql

จะเข้าสู่ shell ของ postgres

postgres-#

2.แสดงรายการผู้ใช้งานใน PostgreSQL ผ่านคำสั่ง

postgres-# \du หรือ \du+

จะแสดงรายชื่อผู้ใช้งานในฐานข้อมูล

3.สรา้งผู้ใช้งานชื่อ demo โดยกำหนดรหัสผ่านเป็น demo1234 ผ่านคำสั่ง SQL

postgres-# CREATE USER demo WITH PASSWORD 'demo1234';

เมื่อใช้คำสั่ง \du จะพบชื่อผู้ใช้งาน demo ที่สร้างขึ้นใหม่

การมอบสิทธิเจ้าของฐานข้อมูลให้ผู้ใช้งานที่สร้างขึ้น

1.สรา้งฐานข้อมูล demodb ผ่านคำสั่ง SQL

postgres-# CREATE DATABASE demodb;

2.มอบทุกสิทธิในฐานข้อมูล demodb ให้กับผู้ใช้งาน demo ผ่านคำสั่ง

postgres-# GRANT ALL PRIVILEGES ON DATABASE demodb TO demo;

3.มอบสิทธิความเป็นเจ้าของ demodb ให้กับผู้ใช้งาน demo ผ่านคำสั่ง

postgres-# ALTER DATABASE demodb OWNER TO demo;

4.ตรวจสอบความเป็นเจ้าของฐานข้อมูล โดยใช้ \l แสดงรายชื่อฐานข้อมูล

postgres-# \l

ในรายชื่อฐานข้อมูล demodb ที่คอลัมถ์ Owner จะแสดงค่าเป็น demo

หมายเหตุ เราสามารถสร้างฐานข้อมูลพร้อมกำหนดเจ้าของได้ในคำสั่งเดียวผ่าน
postgres-# CREATE DATABASE demodb WITH OWNER demo;
จะเป็นการสรา้งฐานข้อมูล demodb พร้อมกับกำหนดเจ้าของเป็น demo ในคำสั่งเดียว

บทส่งท้าย

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