PostgreSQL provides the COPY command to efficiently transfer data between database tables and files or standard input/output streams. Traditionally, COPY supports formats such as TEXT, CSV, and BINARY.
However, exporting relational data directly as JSON has often required additional SQL functions, such as row_to_json() or json_agg().
PostgreSQL 19 introduces a new capability: native JSON format support for COPY TO.
With this enhancement, we can export table data or query results directly in JSON format without explicitly converting every row using JSON functions.
In this article, I will demonstrate this feature using PostgreSQL 19 and compare the traditional approach with the new syntax.
Preparing the Test Environment
First, let's create a sample table named tbl_person.
vahiddb=# CREATE TABLE tbl_person (
id INTEGER,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
job_title VARCHAR(100),
salary NUMERIC(10,2),
active BOOLEAN,
hire_date DATE,
profile JSONB
);
CREATE TABLE
Next, insert five sample records:
vahiddb=# INSERT INTO tbl_person (
id, first_name, last_name, department,
job_title, salary, active, hire_date, profile
)
VALUES
(1, 'Vahid', 'Yousefzadeh', 'DBA', 'Senior Oracle DBA',
8500, TRUE, '2011-09-01',
'{"city":"Babol","skills":["Oracle","PostgreSQL"]}'),
(2, 'Nader', 'DastNeshan', 'Sports', 'Football Coach',
6500, TRUE, '2015-03-10',
'{"city":"Mazandaran","skills":["Football Coach"]}'),
(3, 'Sadegh', 'Varmaziar', 'Sports', 'Football Coach',
7200, TRUE, '2018-06-15',
'{"city":"Sari","skills":["Football Coach"]}'),
(4, 'Masoud', 'Sori', 'DBA', 'Oracle DBA',
5800, FALSE, '2020-01-20',
'{"city":"Babol","skills":["SQL Server","Oracle"]}'),
(5, 'Payan', 'Rafat', 'Sports', 'Football Coach',
9500, TRUE, '2010-11-05',
'{"city":"Tehran","skills":["Football Coach"]}');
INSERT 0 5
Let's verify the inserted records:
vahiddb=# SELECT * FROM tbl_person;
id | first_name | last_name | department | job_title | salary | active | hire_date | profile
----+------------+-------------+------------+-------------------+---------+--------+------------+-------------------------------------------------------
1 | Vahid | Yousefzadeh | DBA | Senior Oracle DBA | 8500.00 | t | 2011-09-01 | {"city": "Babol", "skills": ["Oracle", "PostgreSQL"]}
2 | Nader | DastNeshan | Sports | Football Coach | 6500.00 | t | 2015-03-10 | {"city": "Mazandaran", "skills": ["Football Coach"]}
3 | Sadegh | Varmaziar | Sports | Football Coach | 7200.00 | t | 2018-06-15 | {"city": "Sari", "skills": ["Football Coach"]}
4 | Masoud | Sori | DBA | Oracle DBA | 5800.00 | f | 2020-01-20 | {"city": "Babol", "skills": ["SQL Server", "Oracle"]}
5 | Payan | Rafat | Sports | Football Coach | 9500.00 | t | 2010-11-05 | {"city": "Tehran", "skills": ["Football Coach"]}
(5 rows)
Traditional Approach: Using row_to_json()
Before PostgreSQL 19, one way to export query results as JSON objects was to use the row_to_json() function.
For example:
vahiddb=# COPY (
SELECT row_to_json(tbl_person) FROM tbl_person
) TO STDOUT;
{"id":1,"first_name":"Vahid","last_name":"Yousefzadeh","department":"DBA","job_title":"Senior Oracle DBA","salary":8500.00,"active":true,"hire_date":"2011-09-01","profile":{"city": "Babol", "skills": ["Oracle", "PostgreSQL"]}}
{"id":2,"first_name":"Nader","last_name":"DastNeshan","department":"Sports","job_title":"Football Coach","salary":6500.00,"active":true,"hire_date":"2015-03-10","profile":{"city": "Mazandaran", "skills": ["Football Coach"]}}
{"id":3,"first_name":"Sadegh","last_name":"Varmaziar","department":"Sports","job_title":"Football Coach","salary":7200.00,"active":true,"hire_date":"2018-06-15","profile":{"city": "Sari", "skills": ["Football Coach"]}}
{"id":4,"first_name":"Masoud","last_name":"Sori","department":"DBA","job_title":"Oracle DBA","salary":5800.00,"active":false,"hire_date":"2020-01-20","profile":{"city": "Babol", "skills": ["SQL Server", "Oracle"]}}
{"id":5,"first_name":"Payan","last_name":"Rafat","department":"Sports","job_title":"Football Coach","salary":9500.00,"active":true,"hire_date":"2010-11-05","profile":{"city": "Tehran", "skills": ["Football Coach"]}}
We can also save the output directly to a file on the database server:
vahiddb=# COPY (
SELECT row_to_json(tbl_person) FROM tbl_person
) TO '/backup/tbl_person.json';
COPY 5
[postgres@OEL98-PG ~]$ cat /backup/tbl_person.json
{"id":1,"first_name":"Vahid","last_name":"Yousefzadeh","department":"DBA","job_title":"Senior Oracle DBA","salary":8500.00,"active":true,"hire_date":"2011-09-01","profile":{"city": "Babol", "skills": ["Oracle", "PostgreSQL"]}}
{"id":2,"first_name":"Nader","last_name":"DastNeshan","department":"Sports","job_title":"Football Coach","salary":6500.00,"active":true,"hire_date":"2015-03-10","profile":{"city": "Mazandaran", "skills": ["Football Coach"]}}
{"id":3,"first_name":"Sadegh","last_name":"Varmaziar","department":"Sports","job_title":"Football Coach","salary":7200.00,"active":true,"hire_date":"2018-06-15","profile":{"city": "Sari", "skills": ["Football Coach"]}}
{"id":4,"first_name":"Masoud","last_name":"Sori","department":"DBA","job_title":"Oracle DBA","salary":5800.00,"active":false,"hire_date":"2020-01-20","profile":{"city": "Babol", "skills": ["SQL Server", "Oracle"]}}
{"id":5,"first_name":"Payan","last_name":"Rafat","department":"Sports","job_title":"Football Coach","salary":9500.00,"active":true,"hire_date":"2010-11-05","profile":{"city": "Tehran", "skills": ["Football Coach"]}}
As shown above, the traditional approach works, but it requires explicitly calling row_to_json().
PostgreSQL 19: Native JSON Format in COPY TO
PostgreSQL 19 simplifies this operation by introducing the FORMAT JSON option. Instead of using row_to_json(), we can directly execute:
vahiddb=# copy tbl_person to '/backup/tbl_person.json' with (format json);
COPY 5
[postgres@OEL98-PG ~]$ cat /backup/tbl_person.json
{"id":1,"first_name":"Vahid","last_name":"Yousefzadeh","department":"DBA","job_title":"Senior Oracle DBA","salary":8500.00,"active":true,"hire_date":"2011-09-01","profile":{"city": "Babol", "skills": ["Oracle", "PostgreSQL"]}}
{"id":2,"first_name":"Nader","last_name":"DastNeshan","department":"Sports","job_title":"Football Coach","salary":6500.00,"active":true,"hire_date":"2015-03-10","profile":{"city": "Mazandaran", "skills": ["Football Coach"]}}
{"id":3,"first_name":"Sadegh","last_name":"Varmaziar","department":"Sports","job_title":"Football Coach","salary":7200.00,"active":true,"hire_date":"2018-06-15","profile":{"city": "Sari", "skills": ["Football Coach"]}}
{"id":4,"first_name":"Masoud","last_name":"Sori","department":"DBA","job_title":"Oracle DBA","salary":5800.00,"active":false,"hire_date":"2020-01-20","profile":{"city": "Babol", "skills": ["SQL Server", "Oracle"]}}
{"id":5,"first_name":"Payan","last_name":"Rafat","department":"Sports","job_title":"Football Coach","salary":9500.00,"active":true,"hire_date":"2010-11-05","profile":{"city": "Tehran", "skills": ["Football Coach"]}}
The output contains the same JSON objects as the previous example.
The important difference is the SQL syntax. We no longer need to explicitly convert each row using row_to_json().
We can also send the output directly to the client using STDOUT:
vahiddb=# COPY tbl_person TO STDOUT WITH (FORMAT JSON);
{"id":1,"first_name":"Vahid","last_name":"Yousefzadeh","department":"DBA","job_title":"Senior Oracle DBA","salary":8500.00,"active":true,"hire_date":"2011-09-01","profile":{"city": "Babol", "skills": ["Oracle", "PostgreSQL"]}}
{"id":2,"first_name":"Nader","last_name":"DastNeshan","department":"Sports","job_title":"Football Coach","salary":6500.00,"active":true,"hire_date":"2015-03-10","profile":{"city": "Mazandaran", "skills": ["Football Coach"]}}
{"id":3,"first_name":"Sadegh","last_name":"Varmaziar","department":"Sports","job_title":"Football Coach","salary":7200.00,"active":true,"hire_date":"2018-06-15","profile":{"city": "Sari", "skills": ["Football Coach"]}}
{"id":4,"first_name":"Masoud","last_name":"Sori","department":"DBA","job_title":"Oracle DBA","salary":5800.00,"active":false,"hire_date":"2020-01-20","profile":{"city": "Babol", "skills": ["SQL Server", "Oracle"]}}
{"id":5,"first_name":"Payan","last_name":"Rafat","department":"Sports","job_title":"Football Coach","salary":9500.00,"active":true,"hire_date":"2010-11-05","profile":{"city": "Tehran", "skills": ["Football Coach"]}}
Notice that PostgreSQL produces one JSON object per row. This format is known as Newline-Delimited JSON (NDJSON).
Using FORCE_ARRAY to Generate a JSON Array
What if an application expects a single JSON document containing an array of objects instead of separate JSON objects on individual lines?
PostgreSQL 19 introduces another option called FORCE_ARRAY.
Let's execute:
vahiddb=# COPY tbl_person TO STDOUT WITH (FORMAT JSON, FORCE_ARRAY);
[
{"id":1,"first_name":"Vahid","last_name":"Yousefzadeh","department":"DBA","job_title":"Senior Oracle DBA","salary":8500.00,"active":true,"hire_date":"2011-09-01","profile":{"city": "Babol", "skills": ["Oracle", "PostgreSQL"]}}
,{"id":2,"first_name":"Nader","last_name":"DastNeshan","department":"Sports","job_title":"Football Coach","salary":6500.00,"active":true,"hire_date":"2015-03-10","profile":{"city": "Mazandaran", "skills": ["Football Coach"]}}
,{"id":3,"first_name":"Sadegh","last_name":"Varmaziar","department":"Sports","job_title":"Football Coach","salary":7200.00,"active":true,"hire_date":"2018-06-15","profile":{"city": "Sari", "skills": ["Football Coach"]}}
,{"id":4,"first_name":"Masoud","last_name":"Sori","department":"DBA","job_title":"Oracle DBA","salary":5800.00,"active":false,"hire_date":"2020-01-20","profile":{"city": "Babol", "skills": ["SQL Server", "Oracle"]}}
,{"id":5,"first_name":"Payan","last_name":"Rafat","department":"Sports","job_title":"Football Coach","salary":9500.00,"active":true,"hire_date":"2010-11-05","profile":{"city": "Tehran", "skills": ["Football Coach"]}}
]
The FORCE_ARRAY option changes the output structure from separate JSON objects into a single JSON array.
This is useful when integrating PostgreSQL with applications and services that expect a complete JSON array.
Exporting Selected Columns
Another useful capability is exporting only the columns required by the consuming application.
For example:
vahiddb=# COPY (SELECT first_name, last_name FROM tbl_person ) TO STDOUT WITH (FORMAT JSON);
{"first_name":"Vahid","last_name":"Yousefzadeh"}
{"first_name":"Nader","last_name":"DastNeshan"}
{"first_name":"Sadegh","last_name":"Varmaziar"}
{"first_name":"Masoud","last_name":"Sori"}
{"first_name":"Payan","last_name":"Rafat"}
As demonstrated, FORMAT JSON also works with query results, not just direct table exports.
This provides flexibility when exporting specific columns, filtering records, joining tables, or generating calculated values.
Top comments (0)