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?

Answer 1

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

Answer 2

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 '|');