Search code examples
sqlpostgresqlimportnullnumeric

"ERROR: invalid input syntax for type numeric: NULL" postgresql

I'm starting to learn sql in porstgresql. I have create a table with numeric(6,2) structure and trying to import data. But data has some row that has NULL value. Now I'm getting "ERROR: invalid input syntax for type numeric: NULL" Now I have at least 20 column that contain that NULL value.

What should I do in that situation? Should I alter all the column manually or is it possible to do this automatically for all the column?

CREATE TABLE ireland_income_gap_bonus (
    id_c smallint,
    companyName text, companies_ID smallint, 
    meanBonus numeric(6,2),
    meanHourly numeric(6,2),
    medianBonus numeric(6,2),
    medianHourly numeric(6,2),
    reportLink text,
    year_ smallint,
    meanHourlyPT numeric(6,2),
    medianHourlyPT numeric(6,2),
    meanHourlyTemp numeric(6,2),
    medianHourlyTemp numeric(6,2),
    perBonusFemale numeric(6,2),
    perBonusMale numeric(6,2),
    perBIKFemale numeric(6,2),
    perBIKMale numeric(6,2),
    pb1Female numeric(6,2),
    pb1Male numeric(6,2),
    pb2Female numeric(6,2),
    pb2Male numeric(6,2),
    pb3Female numeric(6,2), 
    pb3Male numeric(6,2),
    pb4Female numeric(6,2), 
    pb4Male numeric(6,2),
    perEmployeesFemale numeric(6,2),
    perEmployeesMale numeric(6,2),
    commentss text
    
)

Then,

COPY ireland_income_gap_bonus
FROM 'E:\Programming\SQL\Project\Data\Ireland_gender_pays_gap\Ireland_gpg.CSV'
WITH (FORMAT CSV, HEADER)

Return messages,

ERROR: invalid input syntax for type numeric: "NULL" CONTEXT: COPY ireland_income_gap_bonus, line 2, column meanhourlypt: "NULL"

SQL state: 22P02

Solution

  • To treat the string NULL in the csv as null value, use the NULL 'null_string' option in COPY (documented here):

    COPY ireland_income_gap_bonus
FROM 'E:\Programming\SQL\Project\Data\Ireland_gender_pays_gap\Ireland_gpg.CSV'
WITH (FORMAT CSV, HEADER, NULL 'NULL')