- Notifications
You must be signed in to change notification settings - Fork2
Flexible CSV processing for Postgres
License
PostgREST/pg_csv
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
Postgres has CSV support on theCOPY command, butCOPY has problems:
- It uses a special protocol, so it doesn't work with other standard features likeprepared statements,pipeline mode orpgbench.
- Is not composable. You can't use COPY inside CTEs, subqueries, view definitions or as function arguments.
pg_csv offers flexible CSV processing as a solution.
- Includes a CSV aggregate that composes with SQL expressions.
- Native C extension, x2 times faster than SQL queries that try to output CSV (see ourCI results).
- No dependencies except Postgres.
PostgreSQL >= 12 is supported. Clone this repo and run:
make&& make installTo install the extension:
create extension pg_csv;Aggregate that builds a CSV respectingRFC 4180, quoting as required.
createtableprojectsasselect*from (values (1,'Death Star OS',1), (2,'Windows 95 Rebooted',1), (3,'Project "Comma,Please"',2), (4,'Escape ""Plan""',2), (NULL,'NULL & Void',NULL))as _(id, name, client_id);
select csv_agg(x)from projects x; csv_agg-------------------------------- id,name,client_id+1,Death Star OS,1+2,Windows95 Rebooted,1+3,"Project""Comma,Please""",2+4,"Escape""""Plan""""",2+ ,NULL & Void,(1 row)
Custom delimiters can be used to produce different formats like pipe-separated values, tab-separated values or semicolon-separated values.
select csv_agg(x, csv_options(delimiter :='|'))from projects x; csv_agg----------------------------- id|name|client_id+1|Death Star OS|1+2|Windows95 Rebooted|1+3|Open Source Lightsabers|2+4|Galactic Payroll System|2+7|Bugzilla Revival|3(1 row)select csv_agg(x, csv_options(delimiter := E'\t'))from projects x; csv_agg----------------------------------- id name client_id+1 Death Star OS1+2 Windows95 Rebooted1+3 Open Source Lightsabers2+4 Galactic Payroll System2+7 Bugzilla Revival3(1 row)
Note
- Newline, carriage return and double quotes are not supported as delimiters to maintain the integrity of the separated values format.
- The delimiter can only be a single char, if a longer string is specified only the first char will be used.
- Why use a
csv_optionsconstructor function instead of extra arguments? Aggregates don't support named arguments in postgres, see a discussion on#2 (comment).
You can include a byte-order mark (BOM) to make the CSV compatible with Excel.
select csv_agg(x, csv_options(bom := true))from projects x; csv_agg-------------------id,name,client_id+1,Death Star OS,12,Windows95 Rebooted,13,Open Source Lightsabers,24,Galactic Payroll System,25,Bugzilla Revival,3(1 row)
You can omit or include the CSV header.
select csv_agg(x, csv_options(header := false))from projects x; csv_agg-----------------------------1,Death Star OS,1+2,Windows95 Rebooted,1+3,Open Source Lightsabers,2+4,Galactic Payroll System,2+7,Bugzilla Revival,3(1 row)
NULL values are represented by an empty string by default. This can be changed with thenullstr option.
SELECT csv_agg(x, csv_options(nullstr:='<NULL>'))AS bodyFROM projects x; body-------------------------------- id,name,client_id+1,Death Star OS,1+2,Windows95 Rebooted,1+3,"Project""Comma,Please""",2+4,"Escape""""Plan""""",2+<NULL>,NULL & Void,<NULL>(1 row)
- For large bulk exports and imports,
COPY ... CSVshould still be preferred as its faster due to streaming support.
About
Flexible CSV processing for Postgres
Topics
Resources
License
Uh oh!
There was an error while loading.Please reload this page.