I can not import numeric data or dates with empty fields in postgresql

Question

I can not import numeric data or dates with empty fields in postgresql

Navigation

#1 by (0 votes)
#2 by (0 votes)

2

When any numeric numeric date or date column is blank, import error occurs

DATA TO IMPORT:

NU_ANO_CENSO|CO_ENTIDADE|DT_ANO_LETIVO_INICIO|DT_ANO_LETIVO_TERMINO|NU_LATITUDE|NU_LONGITUDE
2017|15547000|06/03/2017|29/12/2017|-1.4708028364433|-48.477029371433
2017|15060381|13/02/2017|28/12/2017|-1.9599968499001|-48.204413671799
2017|15060411|13/02/2017|28/12/2017|                |
2017|15079562|02/03/2017|          |-5.2885284042542|-52.486146949226

TYPES OF FIELDS:

create table ESCOLA_2017
(
NU_ANO_CENSO                  NUMERIC(4),
CO_ENTIDADE                   NUMERIC(8),
DT_ANO_LETIVO_INICIO          DATE,
DT_ANO_LETIVO_TERMINO         DATE,
NU_LATITUDE                   NUMERIC(20,17),
NU_LONGITUDE                  NUMERIC(20,17));

Import command:

COPY ESCOLA_2017 FROM 'C:/CriacaoDB/ESCOLA_2017.txt' using delimiters '|';

ERROR MESSAGE:

ERROR:  invalid input syntax for type numeric: ""
CONTEXT:  COPY ts_escola_17_2, line 2, column nu_latitude: ""
SQL state: 22P02

ERROR:  invalid input syntax for type date: ""
CONTEXT:  COPY ts_escola_17_2, line 2, column dt_ano_letivo_termino: ""
SQL state: 22007

postgresql

asked by anonymous 04.05.2018 / 21:34

2 answers

Display Grid for IE11 How to put a element in Fullscreen [duplicate]

score 0 · Answer 1

The solution will be very simple.

You only need to quote the double quotation marks in "Quote options".

After doing the whole COPY you can indicate the quotation marks with QUOTE, but the default value is already double quotes and this only works for .CSV files, so my recommendation in this case is to use the .CSV extension , instead of .TXT , and try to import normally, indicating the delimiter "|" (pipe).

score 0 · Answer 2

Some fields in your% s of% source file appear to be CSV .

You need to tell the command em branco to interpret these fields as if they were COPY , let's see:

COPY
     ESCOLA_2017
FROM
     'C:/CriacaoDB/ESCOLA_2017.txt'
USING
     DELIMITER '|'
     NULL '';

Reference: link