How to Use The New Functionalities of the Copy Command in PostgreSQL 19

PostgreSQL 19 beta version 1 is now available for testing and evaluation. So in the copy command, there are 5 new features implemented. The copy command in PostgreSQL is actually useful for copying the table data into .csv file or .txt file, and we can also import data from .csv files to their related table. Actually, in every database restore, the copy command runs in the backend.

Inside PostgreSQL, use the \h command to view all the flags used with the copy command, like this.

\h copy

Result :

Command:     COPY
Description: copy data between a file and a table
Syntax:
COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
    [ WHERE condition ]
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]
where option can be one of:
    FORMAT format_name
    FREEZE [ boolean ]
    DELIMITER 'delimiter_character'
    NULL 'null_string'
    DEFAULT 'default_string'
    HEADER [ boolean | integer | MATCH ]
    QUOTE 'quote_character'
    ESCAPE 'escape_character'
    FORCE_ARRAY [ boolean ]
    FORCE_QUOTE { ( column_name [, ...] ) | * }
    FORCE_NOT_NULL { ( column_name [, ...] ) | * }
    FORCE_NULL { ( column_name [, ...] ) | * }
    ON_ERROR error_action
    REJECT_LIMIT maxerror
    ENCODING 'encoding_name'
    LOG_VERBOSITY verbosity
URL: https://www.postgresql.org/docs/19/sql-copy.html

Now, let’s explore the new features of the copy command in PostgreSQL version 19.

1. Skipping multiple headers

Create a .csv file like this.

nano /tmp/sample.csv

Now copy the data below into the .csv file.

Company Employee Data
Generated: 2026-06-17
id,name,salary
1,John,50000
2,Alice,60000

Create a table.

CREATE TABLE employees (
   id INT,
   name TEXT,
   salary NUMERIC
);

Now use the copy command with the attribute HEADER.

COPY employees
FROM '/tmp/employees.csv'
WITH (
FORMAT csv,
HEADER 3
);

Here, PostgreSQL skips these headers

Company Employee Data
Generated: 2026-06-17
id,name,salary

This copy command imports only the data below

1,John,50000
2,Alice,60000

2. ON_ERROR SET_NULL

Now we can easily set null values to the invalid values inserted during the copy command execution.

Create a sample table.

CREATE TABLE sales (
   id INT,
   amount NUMERIC,
   sale_date DATE
);

Create a data file like this.

nano /tmp/sales.csv

Copy the data into this .csv file

1,1000,2026-01-01
2,INVALID,2026-01-02
3,3000,BADDATE

Now use the copy command with the attribute named ON_ERROR_SET_NULL.

COPY sales
FROM '/tmp/sales.csv'
WITH (
   FORMAT csv,
   ON_ERROR SET_NULL
);

In the psql terminal, you get a notice like this.

NOTICE:  in 2 rows, columns were set to null due to data type incompatibility
COPY 3

Now check the data from the table named sales.

select * from sales;

Result :

 id | amount | sale_date  
----+--------+------------
  1 |   1000 | 2026-01-01
  2 |        | 2026-01-02
  3 |   3000 | 

Now we can see that the invalid values here can be seen as null.

This is the purpose of using the attribute named ON_ERROR_SET_NULL in the copy command.

3. COPY TO output JSON format.

This feature is mainly used to see the data in a JSON format.

Create a table and insert some values.

CREATE TABLE products (
   id INT,
   name TEXT,
   price NUMERIC
);
INSERT INTO products VALUES
(1,'Laptop',50000),
(2,'Phone',25000);

Now, try to output the data from this table in JSON format by using the copy command.

COPY products
TO STDOUT
WITH (
   FORMAT json
);

Result :

{"id":1,"name":"Laptop","price":50000}
{"id":2,"name":"Phone","price":25000}

4. COPY TO result as a single JSON array.

Check the copy command without using the option named FORCE_ARRAY.

COPY products
TO STDOUT
WITH (
   FORMAT json
);

Result :

{"id":1,"name":"Laptop","price":50000}
{"id":2,"name":"Phone","price":25000}

Now let’s check with the option FORCE_ARRAY.

COPY products
TO STDOUT
WITH (
   FORMAT json,
   FORCE_ARRAY
);

Result :

[
 {
   "id":1,
   "name":"Laptop",
   "price":50000
 },
 {
   "id":2,
   "name":"Phone",
   "price":25000
 }
]

Now the result is completely inside a single array.

5. COPY TO Partitioned Tables.

Create a parent table and a partition table like this.

CREATE TABLE sales (
id INT,
sale_date DATE,
amount NUMERIC
)
PARTITION BY RANGE (sale_date);
CREATE TABLE sales_2025
PARTITION OF sales
FOR VALUES FROM ('2025-01-01')
TO ('2026-01-01');
CREATE TABLE sales_2026
PARTITION OF sales
FOR VALUES FROM ('2026-01-01')
TO ('2027-01-01');

Insert some value into the partitioned tables.

INSERT INTO sales VALUES
(1,'2025-05-01',1000),
(2,'2026-02-01',2000);

In PostgreSQL version 18 and less than version 18, we actually use the copy command in partition tables like this.

COPY (
   SELECT * FROM sales
)
TO '/tmp/sales.csv'
WITH (FORMAT csv);

But when we use the below copy command in version 18 or a version less than 18, we get an error like this

COPY sales
TO '/tmp/sales.csv'
WITH (FORMAT csv);

Result :

ERROR:  cannot copy from partitioned table "sales"
HINT:  Try the COPY (SELECT ...) TO variant.

This issue is solved in version 19.

select version();

Result :

                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 19beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
(1 row)

Now try the same copy command like this.

 COPY sales
TO '/tmp/sales.csv'
WITH (FORMAT csv);

Result :

COPY 2

Check the structure of the sales tables so you can see their related partition tables also.

\d+ sales

Result :

                                     Partitioned table "public.sales"
  Column   |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
-----------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id        | integer |           |          |         | plain   |             |              | 
 sale_date | date    |           |          |         | plain   |             |              | 
 amount    | numeric |           |          |         | main    |             |              | 
Partition key: RANGE (sale_date)
Partitions:
    sales_2025 FOR VALUES FROM ('2025-01-01') TO ('2026-01-01')
    sales_2026 FOR VALUES FROM ('2026-01-01') TO ('2027-01-01')

By comparing PostgreSQL version 18 and PostgreSQL version 19, the newly implemented 5 features on the copy command are of good value for PostgreSQL users. Now the users can easily copy the data from partitioned tables to .csv with better syntax and the introduction of ON_ERROR_SET_NULL option, entire table output in JSON format, output in single array JSON format, etc., providing valuable insights in PostgreSQL.