How to skip empty line in psql \COPY in PostgreSQL

Question

In PostgreSQL psql, how to make \copy command ignore empty lines in input file?

Here is the code to reproduce it,

create table t1(
  n1 int
);

echo "1
2

" > m.csv

psql> \copy t1(n1) FROM 'm.csv' (delimiter E'\t', NULL 'NULL', FORMAT CSV, HEADER false);

ERROR:  invalid input syntax for integer: ""
CONTEXT:  COPY t1, line 3, column n1: ""

There is an empty line in file m.csv

cat m.csv
1
2
  << empty line

Pavel Stehule · Accepted Answer · 2018-07-12 06:55:47Z

5

PostgreSQL COPY is very strict, so there is not possibility to start COPY in tolerant mode. If it is possible, you can use COPY FROM PROGRAM

[pavel@nemesis ~]$ cat ~/data.csv 
10,20,30
40,50,60

70,80,90

psql -c "\copy f from program ' sed ''/^\s*$/d'' ~/data.csv ' csv" postgres

answered Jul 12, 2018 at 6:55

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

How to skip empty line in psql \COPY in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related