PostgreSQL csv import not working for only integer-CodePudding

I have the following problem using PostgreSQL 14 On Windows 10 with latest updates.

I need to insert values into the following table.

CREATE TABLE StateList (
    ID int GENERATED ALWAYS AS IDENTITY,
    State_Number int NOT NULL,
    ElectionGroup_ID INT NOT NULL,
    Election_Number int NOT NULL,
    UNIQUE (State_Number, ElectionGroup_ID, Election_Number),
    PRIMARY KEY (ID)
);

I want to do the following command:

COPY StateList(Election_Number, State_Number, ElectionGroup_ID )
FROM '...\csvFileStateLists19.csv'
WITH (
    FORMAT CSV,
    DELIMITER ','
    );

the "csvFileStateLists19" being

"19","9","4"
"19","5","238"
"19","5","21"
"19","15","1"
"19","5","10"

It worked fine for another table that used strings and integer. But here I always get:

ERROR: FEHLER: ungültige Eingabesyntax für Typ integer: »19« CONTEXT: COPY statelist, Zeile 1, Spalte election_number: »19«

SQL state: 22P02

Which is usually the sign that the number is an empty string or really not a number. but its not! It's a 19, why doesn't it work?

I generated the file in java, its utf8 encoded, database is "German_Germany.1252"

show client_encoding; => UNICODE show server_encoding; => UTF8 SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = 'database1'; => UTF8

select pg_encoding_to_char(encoding), datcollate, datctype from pg_database where datname = 'database1';

Returns "UTF8" "German_Germany.1252" "German_Germany.1252"

Thank you for your help!

CodePudding user response：

Well, with your input, I get the same error message - just in English, not German - I did it in Vertica, Stonebraker's successor of PosgreSQL, whose CSV parser works very much the same:

COPY statelist FROM LOCAL 'st.csv'  DELIMITER ',' EXCEPTIONS 'st.log';
-- error messages in "st.log"
-- COPY: Input record 1 has been rejected (Invalid integer format '"19"' for column 1 (State_Number)).
-- COPY: Input record 2 has been rejected (Invalid integer format '"19"' for column 1 (State_Number)).
-- COPY: Input record 3 has been rejected (Invalid integer format '"19"' for column 1 (State_Number)).
-- COPY: Input record 4 has been rejected (Invalid integer format '"19"' for column 1 (State_Number)).
-- COPY: Input record 5 has been rejected (Invalid integer format '"19"' for column 1 (State_Number)).

Well, that's no wonder really. "9" is a string literal, not an INTEGER literal. It's a VARCHAR(1) consisting of the numeric letter "9", not an INTEGER.

Try adding the ENCLOSED BY '"' clause. It worked for me:

COPY statelist FROM LOCAL 'st.csv'  DELIMITER ',' ENCLOSED BY '"' EXCEPTIONS 'st.log';
-- out  Rows Loaded 
-- out -------------
-- out            5

SELECT * FROM statelist;
-- out  State_Number | ElectionGroup_ID | Election_Number 
-- out -------------- ------------------ -----------------
-- out            19 |                5 |              10
-- out            19 |                5 |              21
-- out            19 |                5 |             238
-- out            19 |                9 |               4
-- out            19 |               15 |               1

CodePudding user response：

Not an answer just proof that double quoted numeric values in a CSV are not the problem:

cat csv_test.csv 
"19","9"
"19","5"
"19","5"
"19","15"
"19","5"


test(5432)=# \d csv_test 
              Table "public.csv_test"
 Column |  Type   | Collation | Nullable | Default 
-------- --------- ----------- ---------- ---------
 col1   | integer |           |          | 
 col2   | integer |           |          | 

select * from csv_test;
 col1 | col2 
------ ------
(0 rows)

\copy csv_test from 'csv_test.csv' with  csv;
COPY 5

select * from csv_test;
 col1 | col2 
------ ------
   19 |    9
   19 |    5
   19 |    5
   19 |   15
   19 |    5

So now maybe we can get on with answers that solve the issue.