Proposal: Richer Structured Export Support for COPY TO

Dear PostgreSQL Community,

I would like to propose a discussion around enhancing the PostgreSQL COPY
TO command to better support structured data exports that require
additional header, trailer, and metadata information.

Current Limitation

COPY TO with CSV HEADER currently provides the column names as a single
header row. However, some real-world data exchange and batch-processing
requirements need additional information around the exported data.

For example:

File Name: employees.csv
File Generated: 2026-08-12 21:30:00
employee_id,name,department
101,John,Finance
102,Mary,HR
103,David,IT
Total Rows: 3

Today, producing such output generally requires additional scripting or
post-processing around COPY.

Proposed Enhancements

1.

*Multi-line Headers*

Allow users to optionally define one or more descriptive header lines
before the column header.

For example:

File Name: employees.csv
File About: Employee Master Data
Source: HR Database
employee_id,name,department
101,John,Finance
102,Mary,HR

The exact syntax and formatting could be discussed by the community.

2.

*Configurable Automatic Footer or Trailer*

Provide an option to automatically generate a footer or trailer containing
an export count.

The count could represent either:

-

Total data rows exported
-

Total physical lines written

For example:

Total Rows: 3

or:

Total Lines: 4

Some enterprise batch interfaces use fixed-format trailer records, for
example:

T0003

for 3 data rows, or:

T0040

for 40 data rows.

The trailer functionality could potentially allow configuration of:

-

Trailer prefix or identifier, such as T
-

Count type: rows or physical lines
-

Field width
-

Padding character
-

Position of the count
-

Custom trailer text or format

For example:

employee_id,name,department
101,John,Finance
102,Mary,HR
103,David,IT
T0003

The distinction between rows and physical lines is important for CSV
because a single CSV record may contain embedded newline characters.

3.

*Optional Metadata Output*

Provide an option to generate a separate metadata document alongside the
exported data file.

For example:

employees.csv
employees.csv.metadata.json

The metadata could optionally contain information such as:

-

Table or query source
-

Column names
-

Column data types
-

Column order
-

Number of rows exported
-

Number of physical lines written
-

File generation timestamp
-

Encoding
-

COPY formatting options

JSON could be one possible metadata format, although the format and
contents would be open for community discussion.

Potential Use Cases

These capabilities could be useful for:

-

Enterprise batch data exchange
-

ETL and integration pipelines
-

Financial and regulatory reporting
-

Legacy systems requiring header and trailer records
-

Data reconciliation and record-count validation
-

Audit-oriented data exports
-

Interfaces where receiving systems validate record counts using trailer
records

They could also reduce the need for external shell scripts or
application-level post-processing around COPY.

Illustrative Syntax

The following syntax is only intended to demonstrate the concept and is not
a proposed final syntax:

COPY my_table TO '/path/to/output.csv'
WITH CSV HEADER
HEADER LINES (...)
FOOTER ROW COUNT
METADATA FILE '/path/to/output.metadata.json';

The footer/trailer could potentially support a configurable format, for
example:

FOOTER FORMAT 'T{ROW_COUNT:04}';

Again, these examples are only intended to illustrate the desired
capability. The actual syntax and implementation approach would be open for
discussion.

I am not suggesting that all of these capabilities necessarily need to be
implemented as a single feature. They could be considered independently or
as part of a broader structured-export capability.

Discussion Points

I would be interested in the community's feedback on:

-

Whether multi-line headers are appropriate for COPY TO.
-

Whether PostgreSQL should natively generate configurable footer or
trailer records.
-

Whether row count and physical line count should both be supported.
-

Whether generating a separate metadata document is appropriate for COPY.
-

Whether these capabilities should be implemented independently or as
part of a common export framework.
-

Whether existing PostgreSQL functionality or extensions already address
these requirements adequately.
-

Any concerns regarding performance, atomicity, file handling, or
backward compatibility.

This proposal is based on practical data-export and integration
requirements I have encountered, and I would be interested in understanding
whether similar requirements are relevant to other PostgreSQL users.

Thank you for your time and feedback.

Regards,
Mariyan Clement
Database Architectect | PostgreSQL | Oracle
LinkedIn: https://www.linkedin.com/in/mariyanclement/