2

I am trying to import the following csv file into YugaByte DB YSQL. Note that the second entry in each row is a JSON object.

"15-06-2018","{\"file_name\": \"myfile1\", \"remote_ip\": \"X.X.X.X\"}"
"15-06-2018","{\"file_name\": \"myfile2\", \"remote_ip\": \"Y.Y.Y.Y\"}"

My table schema is:

postgres=# create table downloads_raw (request_date text, payload jsonb);

I want the JSON snippet in the imported file to become a JSONB value.

I tried doing the following:

postgres=# COPY downloads_raw FROM 'data.csv';

Hitting the following error:

ERROR:  22P04: missing data for column "payload"
CONTEXT:  COPY downloads_raw, line 1: ""15-06-2018","{\"file_name\": \"myfile1\", \"remote_ip\": \"X.X.X.X\"}""
LOCATION:  NextCopyFrom, copy.c:3443
Time: 2.439 ms
Karthik Ranganathan
  • 677
  • 4
  • 9
  • 2
    Since it’s saying there aren’t two values in the input it may be assuming a different delimiter, try telling it it’s comma separated? Or csv in general `WITH FORMAT CSV` – Sami Kuhmonen Jun 08 '19 at 05:44
  • Hmm, tried `WITH FORMAT CSV`, but the syntax is invalid: `# COPY downloads_raw FROM 'data.csv' WITH FORMAT CSV; ERROR: 42601: syntax error at or near "FORMAT"` Let me figure out how to specify a delimiter. – Karthik Ranganathan Jun 08 '19 at 19:54

1 Answers1

3

You need to specify FORMAT csv and ESCAPE '\'. Also, the format and escape options need to be enclosed in parenthesis. This should work:

COPY downloads_raw FROM 'data.csv' WITH (FORMAT csv, ESCAPE '\');

List of supported options for COPY command can be found here: https://docs.yugabyte.com/latest/api/ysql/commands/cmd_copy/

Neha
  • 116
  • 2