Thread: csv copy error
Readers, Please could anyone help with the following error produced: " ERROR: invalid input syntax for integer: "1,m " CONTEXT: COPY exampletable, line 1, column examplenumber: "1,m " The database commands: " CREATE TABLE exampletable (examplenumber smallint, exampletitle varchar(500) ); " " \copy exampletable from '/local/path/to/examplefile.csv'; " CSV file contents: " 1,m 2,m 9,t "
On 12/29/21 13:08, ourdiaspora wrote: > Readers, > > Please could anyone help with the following error produced: > > " > ERROR: invalid input syntax for integer: "1,m " > CONTEXT: COPY exampletable, line 1, column examplenumber: "1,m " > > The database commands: > > " > CREATE TABLE exampletable (examplenumber smallint, > exampletitle varchar(500) > ); > " > > " > \copy exampletable from '/local/path/to/examplefile.csv'; > " \copy exampletable from '/local/path/to/examplefile.csv with csv'; > > CSV file contents: > > " > 1,m > 2,m > 9,t > " > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Wednesday, December 29, 2021, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 12/29/21 13:08, ourdiaspora wrote:
"
\copy exampletable from '/local/path/to/examplefile.csv';
"
\copy exampletable from '/local/path/to/examplefile.csv with csv';
Right idea though that won’t execute for three reasons (bad quoting, missing parentheses, missing field type for the value csv). The main issue is the OP is accepting all defaults but the file format is not in the default format. Either the format needs to be changed or the OP needs to specify a combination of options that describes the file format. Specifying the csv format I think is a valid solution though there are others. The documentation describes the options, syntax, and effects sufficiently so I will not belabor the details.
David J.
Hi,
Le mer. 29 déc. 2021 à 22:08, ourdiaspora <ourdiaspora@protonmail.com> a écrit :
Readers,
Please could anyone help with the following error produced:
"
ERROR: invalid input syntax for integer: "1,m "
CONTEXT: COPY exampletable, line 1, column examplenumber: "1,m "
The database commands:
"
CREATE TABLE exampletable (examplenumber smallint,
exampletitle varchar(500)
);
"
"
\copy exampletable from '/local/path/to/examplefile.csv';
"
CSV file contents:
"
1,m
2,m
9,t
"
You should tell the CSV command you're using a CSV file. By default, COPY thinks it's a TSV file.
--
Guillaume.
On Thursday, December 30th, 2021 at 7:30 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote: > Hi, > > Le mer. 29 déc. 2021 à 22:08, ourdiaspora <ourdiaspora@protonmail.com> a écrit : > > > > > \copy exampletable from '/local/path/to/examplefile.csv'; > > > > You should tell the CSV command you're using a CSV file. By default, COPY thinks it's a TSV file. > Problem solved with: " ...file.csv' with csv; " Thanks to all.