2

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

linggapratama28
  • 23
  • 6
  • TOOLS-> import/export, have you tried? https://learnsql.com/blog/how-to-import-csv-to-postgresql/ – Kas Nov 29 '22 at 11:11
  • yes, i've tried the tutorial. if you can help me, you can access my data in https://drive.google.com/drive/folders/1Jnewe29ZDJzyxH6XlnzLt4prvnoR11KY?usp=sharing and if you success don't forget to let me know – linggapratama28 Nov 29 '22 at 11:28
  • I'll try. As a quick solution I can recommend different IDE- Dbeaver (community is a free version) and I have never had a problem with importing csv files there – Kas Nov 29 '22 at 11:30
  • I have the same error - it is because first value in gross column is NULL, but technically it shouldnt matter... I think – Kas Nov 29 '22 at 11:41
  • thank you for participating has helped. Hopefully other friends can help with the problems I'm experiencing – linggapratama28 Nov 29 '22 at 12:02

2 Answers2

1

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
Kas
  • 313
  • 1
  • 14
0

  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
Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
  • thanks bro. even though your code doesn't match my solution but it's wide open to how i solve the problem – linggapratama28 Dec 02 '22 at 06:51
  • I don't know what that means. The issue is that you are trying to load an empty string as an `integer` and that will not work. – Adrian Klaver Dec 02 '22 at 15:37
  • it would be nice if you try this dataset https://drive.google.com/drive/u/1/folders/1Jnewe29ZDJzyxH6XlnzLt4prvnoR11KY then you try to import it in pgadmin4. if an error appears (ERROR: invalid input syntax for type integer: "" CONTEXT: COPY films, line 1, column gross: "") and you found the solution, please let me know. because I have data that contains null but can't be imported into pgadmin4 and the error is more or less the same as above. thanks for the help – linggapratama28 Dec 03 '22 at 11:55
  • Updated answer with example of how I got the data to load. The data had multiple issues that needed to be dealt with. – Adrian Klaver Dec 03 '22 at 22:50