How to upload/import NULL values in column from file (csv/text) with data types integer/double precision/bigint/numeric into a PostgreSQL table?

Question

I have some text file. Which I can't import into a PostgreSQL table because some of my columns have NULL values. My table's columns are of type integer, bigint, double precision.

If all column's data types are character varying, it imports well. When the above data types are used, I get an error.

sample table

create table if not exists test
(
col1 character varying,
col2 bigint default 0,
col3 integer default 0,
col4 double precision default 0.0,
col5 double precision default 0.0,
col6 character varying,
col7 character varying)

sample text file:

1234||||0.0566|ab3|
1234||||0.0566|ab3|
1234||2||0.0566|ab3|
1234|9465662698|||0.0566|ab3|

This is the command I am using:

copy test.test1 (col1, col2, col3, col4, col5, col6, col7) FROM '/home/tanzir/Documents/test' DELIMITER '|'

I get an error like:

ERROR:  invalid input syntax for type bigint: ""
CONTEXT:  COPY test1, line 1, column col2: ""

Please help me how to solve it?

@LaurenzAlbe I use PgAdmin4 for test purpose. Usually I upload file via command line. — Tanzir
– Tanzir, Commented Feb 23, 2022 at 7:22
Hello, i tried copiyng both your sample table and text file and import works for me. Can you verify your file that has no characters between |. I assume that somewhere along the line some extra control/escape characters were passed in your file. Use vscode/notepad++ for that. (If you use excel keep in mind that excel tends to change things behind the scenes some times (especially if you have dates/times etc). — Convel
– Convel, Commented Feb 23, 2022 at 8:20

Convel · Accepted Answer · 2022-02-23 10:17:01Z

2

For csv files the below command should suffice

COPY public.test (col1, col2, col3, col4, col5, col6, col7) 
FROM '/home/tanzir/Documents/test' WITH(FORMAT CSV, DELIMITER '|');

For text files use the below command

COPY public.test (col1, col2, col3, col4, col5, col6, col7) 
FROM '/home/tanzir/Documents/test' WITH(FORMAT TEXT, DELIMITER '|', NULL '');

The reason for this behaviour is the below:

NULL: Specifies the string that represents a null value. The default is \N (backslash-N) in text format, and an unquoted empty string in CSV format. You might prefer an empty string even in text format for cases where you don't want to distinguish nulls from empty strings. This option is not allowed when using binary format.

source: https://www.postgresql.org/docs/current/sql-copy.html

ps: Since you don't define FORMAT it uses the default which is TEXT

edited Feb 23, 2022 at 10:17

answered Feb 23, 2022 at 9:54

Convel

1768 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Convel Over a year ago

@Tanzir Hello, you are welcome. May i suggest you pick one of the 2 answers (both are correct) as accepted? That way anyone who see this question in the future can easily spot the answer. Also the person who answered gets more reputation (and if i recall correctly you as well for marking the question as answered). Reputation is usefull to unlock stuf like commenting, put bounty on some questions that need more attention etc

Tanzir Over a year ago

both are correct

Laurenz Albe · Accepted Answer · 2022-02-23 10:12:18Z

0

The problem is that you specified the default text format for COPY, where NULL values are represented by \N. Choose the csv format:

COPY test.test1 FROM ... (FORMAT 'csv', DELIMITER '|');

answered Feb 23, 2022 at 10:12

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

How to upload/import NULL values in column from file (csv/text) with data types integer/double precision/bigint/numeric into a PostgreSQL table?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related