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.
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)
COPY TO only supports
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
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.