How to use the pg_csv extension in PostgreSQL 18

Most PostgreSQL users know about the classic:

\COPY table_name TO '/tmp/table_name.csv' CSV HEADER;

It works well — but it comes with limits:

• Requires filesystem access on the database server
• Not easy to embed inside application queries
• Can’t be combined naturally with filters, joins, or prepared statements

While working on PostgreSQL 18, we explored pg_csv, a lightweight extension that solves a different problem:

Generate CSV directly from SQL queries as text without touching the filesystem.

Instead of exporting tables, pg_csv lets you aggregate query results into CSV format inside SQL itself.

Let’s walk through what it is, how to install it, and real examples.

What is pg_csv?

pg_csv is a small but powerful PostgreSQL extension that brings CSV generation directly into SQL.

Instead of exporting data to files using COPY, pg_csv lets you convert query results into CSV inside the database engine itself and return the output as plain text.

At its core, the extension provides a single aggregate:

csv_agg(anyelement [, csv_options])

This function takes rows and produces properly formatted CSV.

Why pg_csv Exists (and Why COPY Isn’t Always Enough)

PostgreSQL already supports CSV through the COPY command, so you might wonder: why do we need something else?

The answer becomes clear once you start building real applications.

COPY has several practical limitations:

1. It Uses a Special Protocol

COPY operates outside the normal SQL execution flow. Because of that, it doesn’t integrate well with:

• Prepared statements
• Pipeline modes
• Tools like pgbench

If you rely on parameterized queries or modern client workflows, this becomes a blocker.

2. It’s Not Composable

You can’t naturally embed COPY into SQL logic.

For example, COPY cannot be used:

• Inside CTEs
• Inside subqueries
• Inside views

• As a function argument

This makes it hard to build reusable or layered queries.

3. It’s File-Oriented

COPY is designed around files or special client streams. That’s fine for admins, but not ideal for applications that just want CSV as a query result.

Installation from Source

We can built it directly from GitHub using PostgreSQL 18.

1. Clone the repository

git clone https://github.com/PostgREST/pg_csv.git
cd pg_csv

2. Build using PGXS

Make sure your pg_config path matches your PostgreSQL installation.

make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config

3. Install

sudo make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config install

4. Enable the extension

Connect to PostgreSQL and run:

CREATE EXTENSION pg_csv;

Verify:

\dx+ pg_csv

You should see functions like this

postgres=# \dx+ pg_csv
               Objects in extension "pg_csv"
                    Object description                     
-----------------------------------------------------------
 function csv_agg(anyelement)
 function csv_agg(anyelement,csv_options)
 function csv_agg_finalfn(internal)
 function csv_agg_transfn(internal,anyelement)
 function csv_agg_transfn(internal,anyelement,csv_options)
 function csv_options("char",boolean,boolean,text)
 type csv_options
 type csv_options[]
(8 rows)

Basic Example

Create a table:

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

Insert data:

INSERT INTO employees VALUES
(1, 'Alice', 'Developer', 50000),
(2, 'Bob', 'DevOps', 60000),
(3, 'Charlie', 'QA', 45000);

Now generate CSV like result:

SELECT csv_agg(x)
FROM employees x;

Output:

id,name,role,salary
1,Alice,Developer,50000
2,Bob,DevOps,60000
3,Charlie,QA,45000

No files. Just SQL.

Using Prepared Statements

Create the table named sales and insert some values

CREATE TABLE sales (
    id INT,
    product TEXT,
    amount DECIMAL(10,2),
    sale_date DATE
);

Insert some values

INSERT INTO sales VALUES
(1, 'Laptop', 1200.00, '2024-01-15'),
(2, 'Mouse', 25.50, '2024-01-16'),
(3, 'Keyboard', 75.00, '2024-01-17'),
(4, 'Monitor', 220.00, '2024-01-18');

Prepare the csv query

PREPARE sales_csv_report AS
SELECT csv_agg(x)
FROM sales x
WHERE sale_date >= $1;

Execute with the parameter

EXECUTE sales_csv_report('2024-01-16');

You instantly get filtered csv like result :

postgres=# EXECUTE sales_csv_report('2024-01-16');
           csv_agg           
-----------------------------
 id,product,amount,sale_date+
 2,Mouse,25.50,2024-01-16   +
 3,Keyboard,75.00,2024-01-17+
 4,Monitor,220.00,2024-01-18

Customizing CSV Output

pg_csv exposes a csv_options type.

Remove Header

SELECT csv_agg(x, csv_options(header := false))
FROM employees x;

Result :

         csv_agg         
-------------------------
 1,Alice,Developer,50000+
 2,Bob,DevOps,60000     +
 3,Charlie,,45000
(1 row)

Change Delimiter

Pipe:

SELECT csv_agg(x, csv_options(delimiter := '|'))
FROM employees x;

Result :

         csv_agg         
-------------------------
 id|name|role|salary    +
 1|Alice|Developer|50000+
 2|Bob|DevOps|60000     +
 3|Charlie||45000
(1 row)

Semicolon:

SELECT csv_agg(x, csv_options(delimiter := ';'))
FROM employees x;

Result :

         csv_agg         
-------------------------
 id;name;role;salary    +
 1;Alice;Developer;50000+
 2;Bob;DevOps;60000     +
 3;Charlie;;45000
(1 row)

Tab:

SELECT csv_agg(x, csv_options(delimiter := E'\t'))
FROM employees x;

Result :

                csv_agg                
---------------------------------------
 id      name    role    salary       +
 1       Alice   Developer       50000+
 2       Bob     DevOps  60000        +
 3       Charlie         45000
(1 row)

UTF-8 BOM (for Excel)

BOM stands for Byte Order Mark.

It’s a small, invisible sequence of bytes added at the very beginning of a text file

SELECT csv_agg(x, csv_options(bom := true))
FROM employees x;

Result :

         csv_agg         
-------------------------
 id,name,role,salary    +
 1,Alice,Developer,50000+
 2,Bob,DevOps,60000     +
 3,Charlie,QA,45000
(1 row)

Custom NULL Value

First, create a NULL:

UPDATE employees SET role = NULL WHERE id = 3;

Then:

SELECT csv_agg(x, csv_options(nullstr := '<NULL>'))
FROM employees x;

Result:

3,Charlie,,45000

You can replace NULL with anything:

csv_options(nullstr := 'N/A')

Combine Options

SELECT csv_agg(
    x,
    csv_options(header := true, delimiter := '|', nullstr := 'NULL')
)
FROM employees x;

Result :

         csv_agg         
-------------------------
 id|name|role|salary    +
 1|Alice|Developer|50000+
 2|Bob|DevOps|60000     +
 3|Charlie|NULL|45000
(1 row)

Why pg_csv is Different from COPY

Here’s the key difference:

COPY

• Writes to the server filesystem
• Needs elevated permissions
• Works on whole tables

pg_csv

• Returns CSV as query result
• Works with filters and joins
• Safe for application use
• No disk access

In short:

COPY is for database admins.pg_csv is for application developers.

pg_csv is small, focused, and practical. It doesn’t try to replace COPY.Instead, it fills a gap PostgreSQL never officially covered, a feature like query-level CSV generation.