PostgreSQL Question posted in
The official documentation can be found here.

"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:ProgrammingSQLProjectDataIreland_gender_pays_gapIreland_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

Tags:

2

Answers


  1. 0

    Using psql and it’s COPY meta-command copy and the NULL parameter documented here COPY:

    cat test.csv 
id,val
1,test
2,test2
3,null

create table csv_test (id integer, val varchar);

copy csv_test from test.csv with csv header null 'null';
COPY 3

pset null 'NULL'
Null display is "NULL".

select * from csv_test ;
 id |  val  
----+-------
  1 | test
  2 | test2
  3 | NULL

    The issue with this is it assumes that all NULL values are represented by 'null'. If null is also represented by NULL or the unquoted empty string or some other value they will not be converted on import as in:

    cat test.csv 
id,val
1,test
2,test2
3,null
4,NULL

copy csv_test from test.csv with csv header null 'null';
COPY 4

pset null 'REAL NULL'
Null display is "REAL NULL".

select * from csv_test ;
 id |    val    
----+-----------
  1 | test
  2 | test2
  3 | REAL NULL
  4 | NULL
    Login or Signup to reply.
  2. 0

    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:ProgrammingSQLProjectDataIreland_gender_pays_gapIreland_gpg.CSV'
WITH (FORMAT CSV, HEADER, NULL 'NULL')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search