csv copy error

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.