i will import data csv to postgresql via pgAdmin 4. But, there are problem

ERROR: invalid input syntax for type integer: ""
CONTEXT: COPY films, line 1, column gross: ""

i understand about the error that is line 1 column gross there is null value and in some other columns there are also null values. My questions, how to import file csv but in the that file there is null value. I've been search in google but not found similar my case.

CREATE TABLE public.films
(
    id int,
    title varchar,
    release_year float4,
    country varchar,
    duration float4,
    language varchar,
    certification varchar,
    gross int,
    budget int
);

And i try in this code below, but failed

CREATE TABLE public.films
(
    id int,
    title varchar,
    release_year float4 null,
    country varchar null,
    duration float4 null,
    language varchar null,
    certification varchar null,
    gross float4 null,
    budget float4 null
);

error message in image

I've searched on google and on the stackoverflow forums. I hope that someone will help solve my problem

CodePudding user response：

It worked for me: https://learnsql.com/blog/how-to-import-csv-to-postgresql/

a small workaround but it works

1. I created a table
2. I added headers to csv file
3. Right click on the newly created table-> Import/export data, select csv file to upload, go to tab2 - select Header and it should work

CodePudding user response：

1. There is no difference between the two table definitions. A column accepts NULL by default.

2. The issue is not a NULL value but an empty string:

select ''::integer;
ERROR:  invalid input syntax for type integer: ""
LINE 1: select ''::integer;

select null::integer;
 int4 
------
 NULL

Create a staging table that has data type of varchar for the fields that are now integer. Load the data into that table. Then modify the empty string data that will be integer using something like:

update table set gross = nullif(trim(gross), '');

Then move the data to the production table.