can't import files csv in pgAdmin 4

Question

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

Answer 1

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.

This is not a pgAdmin4 issue it is a data issue. Working in psql because it is easier to follow:

CREATE TABLE public.films_text
(
    id varchar,
    title varchar,
    release_year varchar,
    country varchar,
    duration varchar,
    language varchar,
    certification varchar,
    gross varchar,
    budget varchar
);
\copy films_text from '~/Downloads/films.csv' with csv
COPY 4968

CREATE TABLE public.films
(
    id int,
    title varchar,
    release_year float4,
    country varchar,
    duration float4,
    language varchar,
    certification varchar,
    gross int,
    budget int
);
-- Below done because of this value 12215500000 in budget column
alter table films alter COLUMN budget type int8;

INSERT INTO films
SELECT
    id::int,
    title,
    nullif (trim(release_year), '')::real, country, nullif(trim(duration), '')::real,
    LANGUAGE,
    certification,
    nullif (trim(gross), '')::float, nullif(trim(budget), '')::float
FROM
    films_text;

INSERT 0 4968

Answer 2

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