0

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?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Tanzir
  • 1
  • 3
  • You forgot to tell us how you import the data. – Laurenz Albe Feb 23 '22 at 06:59
  • @LaurenzAlbe I use PgAdmin4 for test purpose. Usually I upload file via command line. – Tanzir Feb 23 '22 at 07:22
  • Show the SQL statement. – Laurenz Albe Feb 23 '22 at 07:27
  • 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 Feb 23 '22 at 08:20
  • @Convel Hi, would you like to share your code? – Tanzir Feb 23 '22 at 08:56
  • @LaurenzAlbe copy test.test1 (col1, col2, col3, col4, col5, col6, col7) FROM '/home/tanzir/Documents/test' DELIMITER '|' – Tanzir Feb 23 '22 at 08:58
  • @Convel I attach inside view of sample file. I use sublime text editor & I select all data, there is no extra character. – Tanzir Feb 23 '22 at 09:09

2 Answers2

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

Convel
  • 144
  • 7
  • @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 – Convel Feb 25 '22 at 08:14
  • both are correct – Tanzir Mar 08 '22 at 05:40
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 '|');
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263