Using COPY TO for CSV


After looking through docs on the COPY TO/FROM it seems that the COPY TO only does json… ?

What I’m trying to do is dump one table to CSV, so that I can specify the columns to import using COPY FROM into another table ( which has an extra generated column ).
JSON does not seem to allow for extra columns … ?
( I should note the other table is on another instance elsewhere, otherwise I’d use INSERT INTO () SELECT * FROM blah )

I’ve read the various other tutorials but most seem to assume the CSV is generated elsewhere.
I’ve tried CSV export within DBeaver, but this blows up with memory issues ( 9M+ rows in the table to export ).

Any help will be appreciated.

Many thanks

Indeed COPY TO currently is only supported for JSON files

What do you mean by that?

cr> CREATE TABLE old_table (val integer);                                                                                                                        
CREATE OK, 1 row affected  (1.694 sec)

cr> INSERT INTO old_table (val) VALUES (1),(2),(3);                                                                                                              
INSERT OK, 3 rows affected  (0.197 sec)

cr> COPY old_table TO DIRECTORY '/Users/Sandbox/crate-5.0.0/export';                                                                                      
COPY OK, 3 rows affected  (0.027 sec)

cr> CREATE TABLE new_table (val integer, val_g GENERATED ALWAYS AS val+1);                                                                                       
CREATE OK, 1 row affected  (1.556 sec)

cr> COPY new_table FROM '/Users/Sandbox/crate-5.0.0/export/*';                                                                                            
COPY OK, 3 rows affected  (0.134 sec)

cr> SELECT * FROM new_table;                                                                                                                                     
| val | val_g |
|   1 |     2 |
|   2 |     3 |
|   3 |     4 |
SELECT 3 rows in set (0.014 sec)
1 Like

Yes right, COPY TO only supports JSON.
There is also a feature request at GH (export to csv · Issue #8551 · crate/crate · GitHub), but it lacks a bit the use-case or why post-processing JSON to CSV isn’t an option.

Converting the exported JSONL to CSV could be done e.g. using jq:
jq -r -s '(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv' <YOUR_FILE_NAME>.json

1 Like

Oh that’s odd - it was not doing anything on import, no matter what I tried and so I assumed that it was due to not specifying the columns and/or the table schema being different to the import.

I’ll have to try again, as it must be something else.


OK - it was a mistake from my end.

So with the COPY FROM I was specifying the files individually


which did not do anything ( so therefore I was thinking it was needing to specify columns i.e. CSV ).

So now I tried with a wildcard (*) in the and its working. Sorry!

Thanks for all the help though.

Best regards