ALTER TABLE thay đổi một bảng đã tồn tại. Những dạng bạn cần nhất là ADD COLUMN, RENAME COLUMN ... TO, DROP COLUMN và ALTER COLUMN ... TYPE. Có thể đặt nhiều thao tác trong một câu lệnh, ngăn cách bằng dấu phẩy.
Các hàng có sẵn nhận 'free' ở plan và NULL ở signed_up. Từ PostgreSQL 11, thêm cột có giá trị mặc định là hằng số không ghi lại bảng, nên vẫn nhanh kể cả với bảng lớn. Một giá trị mặc định thay đổi theo thời điểm gọi (volatile) như clock_timestamp() vẫn ghi lại mọi hàng.
Đổi tên cột hoặc bảng
Đổi tên diễn ra tức thì và giữ nguyên dữ liệu, index và ràng buộc. View vẫn hoạt động, vì chúng tham chiếu tới id nội bộ của bảng chứ không phải tên. Những chỗ ghi tên dưới dạng văn bản thì không đổi theo: thân hàm PL/pgSQL và truy vấn trong ứng dụng vẫn dùng tên cũ, nên hãy tìm tên đó trước khi đổi.
Xóa cột
DROP COLUMN xóa cột cùng dữ liệu của nó. Lệnh thất bại nếu có view hoặc khóa ngoại phụ thuộc vào cột. CASCADE xóa luôn những thứ đó, còn IF EXISTS bỏ qua lỗi khi cột đã không còn.
Ở đây PostgreSQL không ghi lại bảng. Nó đánh dấu cột là đã xóa, và dung lượng được lấy lại dần khi các hàng được cập nhật hoặc khi bảng được ghi lại.
Đổi kiểu của cột
ALTER COLUMN ... TYPE chuyển đổi mọi giá trị. Khi không có phép ép kiểu tự động từ kiểu cũ sang kiểu mới, hãy thêm USING với một biểu thức tính ra giá trị mới:
Không có USING, câu lệnh này báo lỗi column "price" cannot be cast automatically to type numeric. Chỉ cần một giá trị không chuyển được, cả câu lệnh thất bại và bảng giữ nguyên.
Hầu hết các lần đổi kiểu đều ghi lại bảng. Có vài trường hợp thì không: tăng độ dài của varchar(n), hoặc đổi varchar thành text.
Đặt hoặc bỏ giá trị mặc định và NOT NULL
SET NOT NULL quét cả bảng và thất bại nếu có hàng nào chứa NULL, nên hãy điền chỗ trống trước:
DROP DEFAULT và DROP NOT NULL hủy các thiết lập đó. Giá trị mặc định mới chỉ áp dụng cho những lần chèn sau này. Các hàng có sẵn giữ nguyên giá trị.
Thêm ràng buộc
ALTER TABLE orders ADD CONSTRAINT total_positive CHECK (total > 0);
ALTER TABLE orders DROP CONSTRAINT total_positive;
Thêm một CHECK hoặc khóa ngoại sẽ quét toàn bộ bảng để kiểm tra. Với bảng lớn và bận, hãy thêm nó với NOT VALID trước, cách này chỉ kiểm tra các hàng mới. Sau đó chạy ALTER TABLE orders VALIDATE CONSTRAINT total_positive;. Lệnh này kiểm tra các hàng cũ dưới một lock yếu hơn, cho phép đọc và ghi tiếp tục. Trang khóa ngoại dùng cùng cách làm này, và tài liệu tham khảo ALTER TABLE liệt kê mọi dạng cùng lock của nó.
Câu hỏi thường gặp
Có đổi được thứ tự cột trong PostgreSQL không?
Không. ALTER TABLE không thể di chuyển cột, và cột mới luôn được thêm vào cuối. Hãy liệt kê các cột theo thứ tự bạn muốn trong SELECT, hoặc tạo một bảng mới theo thứ tự đó rồi sao chép dữ liệu sang.
ALTER TABLE có lock bảng trong PostgreSQL không?
Hầu hết các dạng lấy lock ACCESS EXCLUSIVE, chặn cả đọc lẫn ghi cho tới khi câu lệnh chạy xong. Những thay đổi nhanh (đổi tên, xóa cột, thêm cột có giá trị mặc định là hằng số) chỉ giữ lock trong chốc lát. Đổi kiểu mà phải ghi lại bảng thì giữ lock suốt quá trình ghi lại.
Làm sao thêm nhiều cột cùng lúc trong PostgreSQL?
Ngăn cách các thao tác bằng dấu phẩy trong một câu lệnh: ALTER TABLE users ADD COLUMN phone text, ADD COLUMN city text;. Mọi thao tác trong một ALTER TABLE chạy cùng nhau, với một lock.
Có rollback được ALTER TABLE trong PostgreSQL không?
Được. DDL trong PostgreSQL có tính giao dịch, nên một lệnh ALTER TABLE bên trong BEGIN ... ROLLBACK được hoàn tác hoàn toàn.