0
# COPY Operations
1

2
This document covers PostgreSQL's COPY protocol for high-performance bulk data transfer, which is significantly faster than INSERT statements for loading large amounts of data.
3

4
## Capabilities
5

6
### CopyManager
7

8
Main API for COPY operations.
9

10
```java { .api }
11
package org.postgresql.copy;
12

13
import java.io.IOException;
14
import java.io.InputStream;
15
import java.io.OutputStream;
16
import java.io.Reader;
17
import java.io.Writer;
18
import java.sql.SQLException;
19

20
/**
21
 * Manager for PostgreSQL COPY bulk data operations.
22
 * Access via PGConnection.getCopyAPI().
23
 */
24
public class CopyManager {
25
    /**
26
     * Constructs a CopyManager for the given connection.
27
     *
28
     * @param connection PostgreSQL connection
29
     * @throws SQLException if CopyManager cannot be created
30
     */
31
    public CopyManager(org.postgresql.core.BaseConnection connection)
32
            throws SQLException;
33

34
    /**
35
     * Starts a COPY FROM STDIN operation using low-level API.
36
     * Provides fine control over data transfer.
37
     *
38
     * @param sql COPY FROM STDIN command
39
     * @return CopyIn interface for writing data
40
     * @throws SQLException if operation cannot be started
41
     */
42
    public CopyIn copyIn(String sql) throws SQLException;
43

44
    /**
45
     * Starts a COPY TO STDOUT operation using low-level API.
46
     * Provides fine control over data transfer.
47
     *
48
     * @param sql COPY TO STDOUT command
49
     * @return CopyOut interface for reading data
50
     * @throws SQLException if operation cannot be started
51
     */
52
    public CopyOut copyOut(String sql) throws SQLException;
53

54
    /**
55
     * Starts a bidirectional COPY operation (for replication).
56
     *
57
     * @param sql COPY command
58
     * @return CopyDual interface
59
     * @throws SQLException if operation cannot be started
60
     */
61
    public CopyDual copyDual(String sql) throws SQLException;
62

63
    /**
64
     * Copies data from database to Writer (text format).
65
     * High-level convenience method.
66
     *
67
     * @param sql COPY TO STDOUT command
68
     * @param to Writer to receive data
69
     * @return Number of rows (for server 8.2+; -1 for older)
70
     * @throws SQLException if operation fails
71
     * @throws IOException if I/O error occurs
72
     */
73
    public long copyOut(String sql, Writer to) throws SQLException, IOException;
74

75
    /**
76
     * Copies data from database to OutputStream (binary or text format).
77
     * High-level convenience method.
78
     *
79
     * @param sql COPY TO STDOUT command
80
     * @param to OutputStream to receive data
81
     * @return Number of rows (for server 8.2+; -1 for older)
82
     * @throws SQLException if operation fails
83
     * @throws IOException if I/O error occurs
84
     */
85
    public long copyOut(String sql, OutputStream to) throws SQLException, IOException;
86

87
    /**
88
     * Copies data from Reader to database (text format).
89
     * High-level convenience method.
90
     *
91
     * @param sql COPY FROM STDIN command
92
     * @param from Reader providing data
93
     * @return Number of rows loaded
94
     * @throws SQLException if operation fails
95
     * @throws IOException if I/O error occurs
96
     */
97
    public long copyIn(String sql, Reader from) throws SQLException, IOException;
98

99
    /**
100
     * Copies data from Reader to database with buffer size.
101
     *
102
     * @param sql COPY FROM STDIN command
103
     * @param from Reader providing data
104
     * @param bufferSize Buffer size in bytes
105
     * @return Number of rows loaded
106
     * @throws SQLException if operation fails
107
     * @throws IOException if I/O error occurs
108
     */
109
    public long copyIn(String sql, Reader from, int bufferSize)
110
            throws SQLException, IOException;
111

112
    /**
113
     * Copies data from InputStream to database (binary or text format).
114
     * High-level convenience method.
115
     *
116
     * @param sql COPY FROM STDIN command
117
     * @param from InputStream providing data
118
     * @return Number of rows loaded
119
     * @throws SQLException if operation fails
120
     * @throws IOException if I/O error occurs
121
     */
122
    public long copyIn(String sql, InputStream from) throws SQLException, IOException;
123

124
    /**
125
     * Copies data from InputStream to database with buffer size.
126
     *
127
     * @param sql COPY FROM STDIN command
128
     * @param from InputStream providing data
129
     * @param bufferSize Buffer size in bytes
130
     * @return Number of rows loaded
131
     * @throws SQLException if operation fails
132
     * @throws IOException if I/O error occurs
133
     */
134
    public long copyIn(String sql, InputStream from, int bufferSize)
135
            throws SQLException, IOException;
136

137
    /**
138
     * Copies data from ByteStreamWriter to database.
139
     * Efficient for programmatic data generation.
140
     *
141
     * @param sql COPY FROM STDIN command
142
     * @param from ByteStreamWriter providing data
143
     * @return Number of rows loaded
144
     * @throws SQLException if operation fails
145
     * @throws IOException if I/O error occurs
146
     */
147
    public long copyIn(String sql, org.postgresql.util.ByteStreamWriter from)
148
            throws SQLException, IOException;
149
}
150
```
151

152
**Usage Examples:**
153

154
```java
155
import org.postgresql.PGConnection;
156
import org.postgresql.copy.CopyManager;
157
import java.sql.Connection;
158
import java.sql.SQLException;
159
import java.io.*;
160

161
// Example 1: Copy data FROM file to database
162
public class CopyFromFileExample {
163
    public static long loadFromCSV(Connection conn, String tableName,
164
                                   String filename) throws SQLException, IOException {
165
        PGConnection pgConn = conn.unwrap(PGConnection.class);
166
        CopyManager copyManager = pgConn.getCopyAPI();
167

168
        // COPY command
169
        String sql = String.format("COPY %s FROM STDIN WITH (FORMAT CSV, HEADER)", tableName);
170

171
        // Load from file
172
        try (FileReader reader = new FileReader(filename)) {
173
            return copyManager.copyIn(sql, reader);
174
        }
175
    }
176
}
177

178
// Example 2: Copy data TO file from database
179
public class CopyToFileExample {
180
    public static long exportToCSV(Connection conn, String tableName,
181
                                   String filename) throws SQLException, IOException {
182
        PGConnection pgConn = conn.unwrap(PGConnection.class);
183
        CopyManager copyManager = pgConn.getCopyAPI();
184

185
        // COPY command
186
        String sql = String.format("COPY %s TO STDOUT WITH (FORMAT CSV, HEADER)", tableName);
187

188
        // Export to file
189
        try (FileWriter writer = new FileWriter(filename)) {
190
            return copyManager.copyOut(sql, writer);
191
        }
192
    }
193
}
194

195
// Example 3: Copy data with query
196
public class CopyQueryExample {
197
    public static long exportQueryResults(Connection conn, String filename)
198
            throws SQLException, IOException {
199
        PGConnection pgConn = conn.unwrap(PGConnection.class);
200
        CopyManager copyManager = pgConn.getCopyAPI();
201

202
        // COPY a query result
203
        String sql = "COPY (SELECT id, name, email FROM users WHERE active = true) " +
204
                    "TO STDOUT WITH (FORMAT CSV, HEADER)";
205

206
        try (FileWriter writer = new FileWriter(filename)) {
207
            return copyManager.copyOut(sql, writer);
208
        }
209
    }
210
}
211

212
// Example 4: Binary format copy
213
public class BinaryCopyExample {
214
    public static long loadBinary(Connection conn) throws SQLException, IOException {
215
        PGConnection pgConn = conn.unwrap(PGConnection.class);
216
        CopyManager copyManager = pgConn.getCopyAPI();
217

218
        String sql = "COPY data_table FROM STDIN WITH (FORMAT BINARY)";
219

220
        try (FileInputStream in = new FileInputStream("data.bin")) {
221
            return copyManager.copyIn(sql, in);
222
        }
223
    }
224

225
    public static long exportBinary(Connection conn) throws SQLException, IOException {
226
        PGConnection pgConn = conn.unwrap(PGConnection.class);
227
        CopyManager copyManager = pgConn.getCopyAPI();
228

229
        String sql = "COPY data_table TO STDOUT WITH (FORMAT BINARY)";
230

231
        try (FileOutputStream out = new FileOutputStream("data.bin")) {
232
            return copyManager.copyOut(sql, out);
233
        }
234
    }
235
}
236
```
237

238
### Low-Level COPY API
239

240
Fine-grained control over COPY operations.
241

242
```java { .api }
243
package org.postgresql.copy;
244

245
import java.sql.SQLException;
246

247
/**
248
 * Base interface for COPY operations.
249
 */
250
public interface CopyOperation {
251
    /**
252
     * Cancels the COPY operation.
253
     * Must be called if operation is not completed normally.
254
     *
255
     * @throws SQLException if cancellation fails
256
     */
257
    void cancelCopy() throws SQLException;
258

259
    /**
260
     * Returns the overall format: 0 for text, 1 for binary.
261
     *
262
     * @return Format code
263
     * @throws SQLException if format cannot be determined
264
     */
265
    int getFormat() throws SQLException;
266

267
    /**
268
     * Returns the format for a specific field.
269
     *
270
     * @param field Field number (0-based)
271
     * @return Format code for field
272
     * @throws SQLException if format cannot be determined
273
     */
274
    int getFieldFormat(int field) throws SQLException;
275

276
    /**
277
     * Returns whether the COPY operation is still active.
278
     *
279
     * @return true if operation is active
280
     */
281
    boolean isActive();
282

283
    /**
284
     * Returns the number of fields in each row.
285
     *
286
     * @return Field count
287
     * @throws SQLException if count cannot be determined
288
     */
289
    int getFieldCount() throws SQLException;
290

291
    /**
292
     * Returns the number of rows handled so far.
293
     *
294
     * @return Row count
295
     */
296
    long getHandledRowCount();
297
}
298
```
299

300
### CopyIn Interface
301

302
Interface for COPY FROM STDIN operations.
303

304
```java { .api }
305
package org.postgresql.copy;
306

307
import org.postgresql.util.ByteStreamWriter;
308
import java.sql.SQLException;
309

310
/**
311
 * Interface for copying data FROM client TO database.
312
 */
313
public interface CopyIn extends CopyOperation {
314
    /**
315
     * Writes data to the COPY operation.
316
     *
317
     * @param buf Byte array containing data
318
     * @param off Offset in array
319
     * @param siz Number of bytes to write
320
     * @throws SQLException if write fails
321
     */
322
    void writeToCopy(byte[] buf, int off, int siz) throws SQLException;
323

324
    /**
325
     * Writes data from ByteStreamWriter.
326
     *
327
     * @param from Data source
328
     * @throws SQLException if write fails
329
     */
330
    void writeToCopy(ByteStreamWriter from) throws SQLException;
331

332
    /**
333
     * Flushes buffered data to server.
334
     *
335
     * @throws SQLException if flush fails
336
     */
337
    void flushCopy() throws SQLException;
338

339
    /**
340
     * Completes the COPY operation successfully.
341
     * Must be called to finalize the operation.
342
     *
343
     * @return Number of rows loaded
344
     * @throws SQLException if completion fails
345
     */
346
    long endCopy() throws SQLException;
347
}
348
```
349

350
**Usage Examples:**
351

352
```java
353
import org.postgresql.PGConnection;
354
import org.postgresql.copy.CopyIn;
355
import java.sql.Connection;
356
import java.sql.SQLException;
357
import java.nio.charset.StandardCharsets;
358

359
// Example 1: Low-level COPY IN
360
public class LowLevelCopyInExample {
361
    public static void copyInManually(Connection conn) throws SQLException {
362
        PGConnection pgConn = conn.unwrap(PGConnection.class);
363
        String sql = "COPY users (name, email) FROM STDIN WITH (FORMAT CSV)";
364

365
        CopyIn copyIn = pgConn.getCopyAPI().copyIn(sql);
366

367
        try {
368
            // Write CSV rows
369
            String row1 = "John Doe,john@example.com\n";
370
            String row2 = "Jane Smith,jane@example.com\n";
371

372
            copyIn.writeToCopy(row1.getBytes(StandardCharsets.UTF_8), 0,
373
                    row1.getBytes(StandardCharsets.UTF_8).length);
374
            copyIn.writeToCopy(row2.getBytes(StandardCharsets.UTF_8), 0,
375
                    row2.getBytes(StandardCharsets.UTF_8).length);
376

377
            // Complete the operation
378
            long rows = copyIn.endCopy();
379
            System.out.println("Loaded " + rows + " rows");
380

381
        } catch (SQLException e) {
382
            // Cancel on error
383
            if (copyIn.isActive()) {
384
                copyIn.cancelCopy();
385
            }
386
            throw e;
387
        }
388
    }
389
}
390

391
// Example 2: Streaming data generation
392
public class StreamingCopyExample {
393
    public static void generateAndCopy(Connection conn, int numRows)
394
            throws SQLException {
395
        PGConnection pgConn = conn.unwrap(PGConnection.class);
396
        String sql = "COPY large_table (id, value) FROM STDIN WITH (FORMAT CSV)";
397

398
        CopyIn copyIn = pgConn.getCopyAPI().copyIn(sql);
399

400
        try {
401
            StringBuilder batch = new StringBuilder();
402
            int batchSize = 1000;
403

404
            for (int i = 0; i < numRows; i++) {
405
                // Generate row
406
                batch.append(i).append(",").append("Value ").append(i).append("\n");
407

408
                // Write in batches
409
                if ((i + 1) % batchSize == 0 || i == numRows - 1) {
410
                    byte[] bytes = batch.toString().getBytes(StandardCharsets.UTF_8);
411
                    copyIn.writeToCopy(bytes, 0, bytes.length);
412
                    batch.setLength(0); // Clear buffer
413
                }
414
            }
415

416
            long rows = copyIn.endCopy();
417
            System.out.println("Generated and loaded " + rows + " rows");
418

419
        } catch (SQLException e) {
420
            if (copyIn.isActive()) {
421
                copyIn.cancelCopy();
422
            }
423
            throw e;
424
        }
425
    }
426
}
427
```
428

429
### CopyOut Interface
430

431
Interface for COPY TO STDOUT operations.
432

433
```java { .api }
434
package org.postgresql.copy;
435

436
import java.sql.SQLException;
437

438
/**
439
 * Interface for copying data FROM database TO client.
440
 */
441
public interface CopyOut extends CopyOperation {
442
    /**
443
     * Reads a row of data from the COPY operation.
444
     * Blocks until data is available or operation completes.
445
     *
446
     * @return Byte array containing one row, or null if no more data
447
     * @throws SQLException if read fails
448
     */
449
    byte[] readFromCopy() throws SQLException;
450

451
    /**
452
     * Reads a row of data with optional blocking.
453
     *
454
     * @param block If true, blocks until data available; if false, returns immediately
455
     * @return Byte array containing one row, or null if no data available
456
     * @throws SQLException if read fails
457
     */
458
    byte[] readFromCopy(boolean block) throws SQLException;
459
}
460
```
461

462
**Usage Examples:**
463

464
```java
465
import org.postgresql.PGConnection;
466
import org.postgresql.copy.CopyOut;
467
import java.sql.Connection;
468
import java.sql.SQLException;
469
import java.io.FileOutputStream;
470
import java.io.IOException;
471

472
// Example: Low-level COPY OUT
473
public class LowLevelCopyOutExample {
474
    public static void copyOutManually(Connection conn, String filename)
475
            throws SQLException, IOException {
476
        PGConnection pgConn = conn.unwrap(PGConnection.class);
477
        String sql = "COPY users TO STDOUT WITH (FORMAT CSV, HEADER)";
478

479
        CopyOut copyOut = pgConn.getCopyAPI().copyOut(sql);
480

481
        try (FileOutputStream out = new FileOutputStream(filename)) {
482
            byte[] row;
483
            while ((row = copyOut.readFromCopy()) != null) {
484
                out.write(row);
485
            }
486

487
            long rows = copyOut.getHandledRowCount();
488
            System.out.println("Exported " + rows + " rows");
489

490
        } catch (SQLException | IOException e) {
491
            if (copyOut.isActive()) {
492
                copyOut.cancelCopy();
493
            }
494
            throw e;
495
        }
496
    }
497
}
498
```
499

500
### CopyDual Interface
501

502
Interface for bidirectional COPY operations, combining both CopyIn and CopyOut capabilities. Primarily used for PostgreSQL logical replication via the COPY protocol.
503

504
```java { .api }
505
package org.postgresql.copy;
506

507
/**
508
 * Bidirectional COPY interface combining CopyIn and CopyOut.
509
 * Used primarily for PostgreSQL replication via COPY protocol.
510
 * Extends both CopyIn and CopyOut, providing all methods from both interfaces.
511
 *
512
 * @see CopyIn
513
 * @see CopyOut
514
 */
515
public interface CopyDual extends CopyIn, CopyOut {
516
    // Inherits all methods from CopyIn:
517
    // - writeToCopy(byte[], int, int)
518
    // - writeToCopy(ByteStreamWriter)
519
    // - flushCopy()
520
    // - endCopy()
521
    //
522
    // Inherits all methods from CopyOut:
523
    // - readFromCopy()
524
    // - readFromCopy(boolean)
525
    //
526
    // Inherits all methods from CopyOperation:
527
    // - cancelCopy()
528
    // - getFormat()
529
    // - getFieldFormat(int)
530
    // - isActive()
531
    // - getFieldCount()
532
    // - getHandledRowCount()
533
}
534
```
535

536
**Usage Example:**
537

538
```java
539
import org.postgresql.PGConnection;
540
import org.postgresql.copy.CopyDual;
541
import java.sql.Connection;
542
import java.sql.SQLException;
543

544
// Example: Bidirectional COPY for replication
545
public class ReplicationCopyExample {
546
    public static void replicationCopy(Connection conn) throws SQLException {
547
        PGConnection pgConn = conn.unwrap(PGConnection.class);
548

549
        // Start bidirectional COPY for replication
550
        String sql = "COPY pg_logical_slot_get_binary_changes('slot_name', NULL, NULL) TO STDOUT";
551

552
        CopyDual copyDual = pgConn.getCopyAPI().copyDual(sql);
553

554
        try {
555
            // Can both read and write through the same interface
556
            byte[] data;
557
            while ((data = copyDual.readFromCopy()) != null) {
558
                // Process replication data
559
                processReplicationData(data);
560

561
                // Can also write feedback/acknowledgments
562
                // copyDual.writeToCopy(feedback, 0, feedback.length);
563
                // copyDual.flushCopy();
564
            }
565

566
        } catch (SQLException e) {
567
            if (copyDual.isActive()) {
568
                copyDual.cancelCopy();
569
            }
570
            throw e;
571
        }
572
    }
573

574
    private static void processReplicationData(byte[] data) {
575
        // Process replication data
576
    }
577
}
578
```
579

580
### ByteStreamWriter Interface
581

582
Interface for writing byte streams efficiently to COPY operations without requiring data to be assembled into a single in-memory buffer.
583

584
```java { .api }
585
package org.postgresql.util;
586

587
import java.io.IOException;
588
import java.io.OutputStream;
589

590
/**
591
 * Interface for efficiently writing byte streams to COPY operations.
592
 * Useful when data is stored off-heap or in non-contiguous buffers.
593
 */
594
public interface ByteStreamWriter {
595
    /**
596
     * Returns the total length of the stream in bytes.
597
     * Must be known ahead of time before calling writeTo().
598
     *
599
     * @return Number of bytes in the stream
600
     */
601
    int getLength();
602

603
    /**
604
     * Writes the data to the provided target.
605
     * Must not write more than getLength() bytes.
606
     *
607
     * @param target Target to write data to
608
     * @throws IOException if write fails or if more than getLength() bytes are written
609
     */
610
    void writeTo(ByteStreamTarget target) throws IOException;
611

612
    /**
613
     * Creates a ByteStreamWriter from one or more ByteBuffers.
614
     *
615
     * @param buf ByteBuffer(s) to write
616
     * @return ByteStreamWriter for the buffer(s)
617
     */
618
    static ByteStreamWriter of(java.nio.ByteBuffer... buf);
619

620
    /**
621
     * Target interface for writing byte streams.
622
     */
623
    interface ByteStreamTarget {
624
        /**
625
         * Provides an OutputStream to write bytes to.
626
         *
627
         * @return OutputStream for writing
628
         */
629
        OutputStream getOutputStream();
630
    }
631
}
632
```
633

634
**Usage Example:**
635

636
```java
637
import org.postgresql.util.ByteStreamWriter;
638
import java.nio.ByteBuffer;
639
import java.sql.PreparedStatement;
640

641
// Example: Using ByteStreamWriter with direct ByteBuffer
642
public class ByteStreamWriterExample {
643
    public static void useByteStreamWithBuffer(Connection conn, ByteBuffer buffer)
644
            throws SQLException {
645
        String sql = "INSERT INTO data_table (bytes) VALUES (?)";
646

647
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
648
            // Create ByteStreamWriter from ByteBuffer
649
            ByteStreamWriter writer = ByteStreamWriter.of(buffer);
650

651
            // Use setObject to pass the ByteStreamWriter
652
            pstmt.setObject(1, writer);
653
            pstmt.executeUpdate();
654
        }
655
    }
656

657
    // Example: Custom ByteStreamWriter implementation
658
    public static class CustomByteStreamWriter implements ByteStreamWriter {
659
        private final byte[] data;
660

661
        public CustomByteStreamWriter(byte[] data) {
662
            this.data = data;
663
        }
664

665
        @Override
666
        public int getLength() {
667
            return data.length;
668
        }
669

670
        @Override
671
        public void writeTo(ByteStreamTarget target) throws IOException {
672
            target.getOutputStream().write(data);
673
        }
674
    }
675
}
676
```
677

678
### Performance Tips
679

680
**Best Practices for COPY Operations:**
681

682
1. **Use COPY for bulk operations (10,000+ rows)**
683
   ```
684
   COPY is 5-10x faster than INSERT for large datasets
685
   ```
686

687
2. **Disable indexes/constraints temporarily for very large loads**
688
   ```sql
689
   ALTER TABLE my_table DROP CONSTRAINT my_constraint;
690
   -- COPY data
691
   ALTER TABLE my_table ADD CONSTRAINT my_constraint ...;
692
   ```
693

694
3. **Use binary format for better performance**
695
   ```sql
696
   COPY table FROM STDIN WITH (FORMAT BINARY)
697
   ```
698

699
4. **Buffer data when generating programmatically**
700
   ```java
701
   // Write in batches rather than row-by-row
702
   for (int i = 0; i < 1000; i++) {
703
       buffer.append(generateRow(i));
704
   }
705
   copyIn.writeToCopy(buffer.toString().getBytes());
706
   ```
707

708
5. **Consider parallelization for very large datasets**
709
   ```java
710
   // Split data and use multiple connections in parallel
711
   // Each connection copies a partition of the data
712
   ```
713

714
6. **Handle errors properly**
715
   ```java
716
   try {
717
       copyIn.endCopy();
718
   } catch (SQLException e) {
719
       if (copyIn.isActive()) {
720
           copyIn.cancelCopy(); // Always cancel on error
721
       }
722
       throw e;
723
   }
724
   ```
725

726
### COPY Format Options
727

728
**Common COPY options:**
729

730
```sql
731
-- CSV format with header
732
COPY table FROM STDIN WITH (FORMAT CSV, HEADER)
733

734
-- CSV with custom delimiter
735
COPY table FROM STDIN WITH (FORMAT CSV, DELIMITER '|')
736

737
-- CSV with quote character
738
COPY table FROM STDIN WITH (FORMAT CSV, QUOTE '"')
739

740
-- CSV with NULL representation
741
COPY table FROM STDIN WITH (FORMAT CSV, NULL 'NULL')
742

743
-- Binary format (fastest)
744
COPY table FROM STDIN WITH (FORMAT BINARY)
745

746
-- Text format (default)
747
COPY table FROM STDIN WITH (FORMAT TEXT)
748

749
-- Specific columns only
750
COPY table (col1, col2, col3) FROM STDIN WITH (FORMAT CSV)
751

752
-- Copy from query result
753
COPY (SELECT * FROM table WHERE condition) TO STDOUT WITH (FORMAT CSV)
754
```
755

756
**Usage Example:**
757

758
```java
759
// Custom delimited format
760
public class CustomFormatExample {
761
    public static long loadPipeDelimited(Connection conn, Reader reader)
762
            throws SQLException, IOException {
763
        PGConnection pgConn = conn.unwrap(PGConnection.class);
764
        CopyManager copyManager = pgConn.getCopyAPI();
765

766
        String sql = "COPY users FROM STDIN WITH (FORMAT CSV, DELIMITER '|', " +
767
                    "HEADER false, NULL 'NULL')";
768

769
        return copyManager.copyIn(sql, reader);
770
    }
771
}
772
```
773