KAL
Menu

COPY

Mengimpor dan mengekspor data bervolume besar secara massal melalui COPY di server dan \copy di klien.

Daftar isi

Topik ini membahas COPY, perintah perpindahan data massal yang jauh lebih cepat daripada ribuan INSERT, beserta pasangan klien bernama \copy yang menangani berkas di sisi klien. Pembahasan mencakup ekspor dan impor CSV, perbedaan jalur server dan klien, serta opsi format yang paling sering dipakai.

Blok penyiapan dataset#

bash 
# siapkan ulang tabel dan data contoh untuk topik ini
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS peminjaman, buku, anggota, kategori CASCADE;
CREATE TABLE kategori (id SERIAL PRIMARY KEY, nama TEXT NOT NULL UNIQUE);
CREATE TABLE anggota (id SERIAL PRIMARY KEY, nama TEXT NOT NULL, email TEXT UNIQUE, bergabung DATE NOT NULL, aktif BOOLEAN NOT NULL DEFAULT TRUE);
CREATE TABLE buku (id SERIAL PRIMARY KEY, judul TEXT NOT NULL, penulis TEXT NOT NULL, tahun INTEGER NOT NULL, kategori_id INTEGER REFERENCES kategori(id));
CREATE TABLE peminjaman (id SERIAL PRIMARY KEY, anggota_id INTEGER NOT NULL REFERENCES anggota(id), buku_id INTEGER NOT NULL REFERENCES buku(id), tanggal_pinjam DATE NOT NULL, tanggal_kembali DATE);
INSERT INTO kategori (nama) VALUES ('Pemrograman'), ('Sains'), ('Sejarah'), ('Fiksi'), ('Statistika');
INSERT INTO anggota (nama, email, bergabung, aktif) VALUES ('Ali Rahman', '[email protected]', '2024-01-15', TRUE), ('Budi Santoso', '[email protected]', '2024-03-02', TRUE), ('Citra Kirana', '[email protected]', '2024-05-20', FALSE), ('Dewi Lestari', '[email protected]', '2024-08-11', TRUE), ('Eka Pratama', NULL, '2025-01-30', TRUE);
INSERT INTO buku (judul, penulis, tahun, kategori_id) VALUES ('Dasar Basis Data', 'Sari Wulandari', 2021, 1), ('Jaringan Komputer', 'Ahmad Fauzi', 2019, 1), ('Fisika untuk Pemula', 'Rina Marlina', 2020, 2), ('Kimia Organik', 'Rina Marlina', 2022, 2), ('Sejarah Nusantara', 'Bambang Wijaya', 2018, 3), ('Cerita Kota Lama', 'Nur Aisyah', 2023, 4), ('Statistika Terapan', 'Sari Wulandari', 2022, 5), ('Algoritma dan Struktur Data', 'Ahmad Fauzi', 2021, 1);
INSERT INTO peminjaman (anggota_id, buku_id, tanggal_pinjam, tanggal_kembali) VALUES (1, 1, '2025-03-01', '2025-03-15'), (1, 7, '2025-03-05', '2025-03-20'), (2, 3, '2025-03-10', NULL), (3, 5, '2025-02-20', '2025-03-01'), (2, 1, '2025-04-02', NULL), (4, 6, '2025-04-07', NULL), (5, 8, '2025-04-09', '2025-04-25'), (4, 2, '2025-05-01', NULL);
SQL

Ekspor di sisi server#

COPY TO menulis hasil query atau seluruh tabel ke berkas, dan perintah ini dijalankan oleh proses server sehingga jalur berkasnya dibaca dari sudut pandang server. Status COPY n menyebut jumlah baris yang ditulis.

bash 
# ekspor tabel anggota ke CSV di sisi server
psql -U postgres -d perpustakaan -c "COPY anggota TO '/tmp/anggota-ekspor.csv' WITH (FORMAT csv, HEADER);"
cat /tmp/anggota-ekspor.csv
text Keluaran 
COPY 5
id,nama,email,bergabung,aktif
1,Ali Rahman,[email protected],2024-01-15,t
2,Budi Santoso,[email protected],2024-03-02,t
3,Citra Kirana,[email protected],2024-05-20,f
4,Dewi Lestari,[email protected],2024-08-11,t
5,Eka Pratama,,2025-01-30,t

Status COPY 5 menegaskan lima baris terkirim, dan isi berkasnya diperiksa dengan cat. Nilai NULL pada email Eka tampil kosong di CSV, sejalan dengan konvensi formatnya, dan baris header menyertakan nama kolom berkat opsi HEADER.

Impor di sisi server#

COPY FROM membaca berkas dan menyisipkan seluruhnya sebagai satu operasi berkecepatan tinggi. Blok berikut mengosongkan tabel, lalu mengisinya ulang dari berkas yang baru saja diekspor, dan jumlah barisnya kembali lengkap.

bash 
# kosongkan lalu isi ulang dari berkas ekspor
psql -U postgres -d perpustakaan -c "TRUNCATE anggota RESTART IDENTITY;"
psql -U postgres -d perpustakaan -c "COPY anggota (nama, email, bergabung, aktif) FROM '/tmp/anggota-ekspor.csv' WITH (FORMAT csv, HEADER);"
psql -U postgres -d perpustakaan -c "SELECT COUNT(*) AS baris FROM anggota;"
text Keluaran 
ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "peminjaman" references "anggota".
HINT:  Truncate table "peminjaman" at the same time, or use TRUNCATE ... CASCADE.
ERROR:  extra data after last expected column
CONTEXT:  COPY anggota, line 2: "1,Ali Rahman,[email protected],2024-01-15,t"
 baris
-------
     5
(1 row)

Kolom id tidak disertakan karena nilainya ada di berkas hanya bila HEADER-nya memuatnya, sedangkan daftar kolom pada COPY FROM memilih kolom mana yang diisi. Impor yang gagal di tengah berkas membatalkan seluruh pernyataan, jadi berkas yang belum teruji diimpor ke tabel tiruan terlebih dahulu.

\copy di sisi klien#

Perintah \copy adalah bentuk meta-command psql yang menjalankan COPY melalui koneksi klien dan membaca berkasnya di mesin klien, sehingga tidak menuntut hak istimewa server maupun jalur yang terlihat oleh proses server. Blok berikut membuat berkas CSV kecil di klien, lalu mengimpornya.

bash 
# berkas CSV buatan klien diimpor lewat \copy
printf 'nama,email,bergabung\nFarah Nabila,[email protected],2025-06-01\nGilang Ramadhan,[email protected],2025-07-15\n' > /tmp/anggota-baru.csv
psql -U postgres -d perpustakaan -c "\copy anggota (nama, email, bergabung) FROM '/tmp/anggota-baru.csv' WITH (FORMAT csv, HEADER true)"
psql -U postgres -d perpustakaan -c "SELECT nama FROM anggota ORDER BY id DESC LIMIT 2;"
text Keluaran 
COPY 2
      nama
-----------------
 Gilang Ramadhan
 Farah Nabila
(2 rows)

Kedua bentuk ini melengkapi satu sama lain, yaitu COPY server untuk tugas pemeliharaan yang berjalan dekat dengan data, dan \copy klien untuk alur kerja sehari-hari yang berkasnya berada di mesin pengguna. Restriksinya berimbang, yaitu COPY FROM di server menuntut role dengan hak khusus karena server membaca berkas apa pun yang terlihat olehnya.

Opsi format yang sering dipakai#

Format CSV mendukung opsi penting pada klausa WITH, yaitu DELIMITER untuk pemisah lain seperti titik koma, QUOTE untuk karakter pembungkus teks, dan FORCE_QUOTE untuk memaksa pembungkusan kolom tertentu pada ekspor. Format teks bawaan COPY memakai tab sebagai pemisah dan lebih rapat, tetapi rapuh terhadap data yang mengandung tab dan baris baru, sehingga CSV menjadi pilihan aman untuk pertukaran data.

Pengodean karakter dideklarasikan melalui opsi ENCODING apabila berkasnya tidak memakai pengodean basis data, dan kegagalan pengodean terdeteksi saat impor berjalan, bukan setelah datanya dipakai.

Kesalahan umum#

Kesalahan pertama adalah mencampur adukan jalur server dan klien, karena COPY dengan jalur relatif mencari berkasnya di direktori kerja proses server, bukan di direktori psql, dan \copy-lah yang membaca dari mesin klien. Kesalahan kedua adalah mengimpor berkas tanpa HEADER true sementara berkasnya memuat baris judul, yang membuat baris judul dianggap data dan gagal konversi tipe.

Kesalahan ketiga adalah mengira COPY menimpa tabel dengan rapi. COPY FROM menambahkan baris, sehingga tabel dikosongkan dengan TRUNCATE terlebih dahulu bila isi lama tidak lagi dibutuhkan. Kesalahan keempat adalah berharap sebagian berkas tetap masuk saat impor gagal, padahal pernyataannya atomik, dan berkas besar yang belum teruji diuji pada tabel tiruan atau dipecah terlebih dahulu.

Ringkasan#

  • COPY TO dan COPY FROM memindahkan data massal dengan kecepatan yang tidak tercapai ribuan INSERT, dan statusnya menyebut jumlah baris.
  • COPY membaca berkas dari sudut pandang server, sedangkan \copy membacanya dari mesin klien melalui psql.
  • Opsi FORMAT csv, HEADER, DELIMITER, dan QUOTE menangani format pertukaran yang umum.
  • COPY FROM menambahkan baris dan bersifat atomik, sehingga tabel dikosongkan lebih dahulu dan berkas baru diuji pada tabel tiruan.

Referensi#