CODEWITHSUNDEEP
csvcockroachdb
gubaer
gubaer

Reputation: 236

How to import a csv file into a crdb table with a column of type SERIAL?

I have a table with a column of type SERIAL:

IMPORT TABLE osm.node_tags (
    id SERIAL NOT NULL,
    node_id INT NOT NULL,
    key STRING NOT NULL,
    value STRING NOT NULL,
    PRIMARY KEY (id)
)
CSV DATA ('s3://cockroach-import/node-keys.csv?<snip>}')
WITH
    temp = 's3://cockroach-import/?<snip>',
    delimiter = ','
;

The csv file to import 3 columns: node_id, key, and value. I'd like the first one, id, to be assigned by crdb while importing the data.

The import fails with the following error:

Error: pq: s3://cockroach-import/node-keys.csv?<snip>: row 1: expected 4 fields, got 3

Is there any workaround for this?

Upvotes: 1

Views: 466

Answers (1)

David
David

Reputation: 499

Unfortunately there's no good way to do this during IMPORT currently.

Currently your options are either to add IDs either before or after IMPORT.

To do it after, you'd just import the file to an intermediate table without the id column, then use something like INSERT INTO real_table (SELECT * from imported_table) to copy it over. This unfortunately will be slow or even time out if the table is very large.

To do it before importing the table, you could use something like awk to prepend the line number to every line, essentially generating IDs in the CSV before IMPORT reads it, e.g.

awk '{printf("%d, %s\n", NR, $0)}' data.csv

Upvotes: 1

Related Questions