I cannot import numeric data or dates with empty fields in postgresql

Asked

Viewed 360 times

1

When any Numeric or date type gift column is blank error occurs on import

DATA TO BE IMPORTED:

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

FIELD TYPES:

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

Command p/ import:

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

2 answers

0

The solution will be very simple.

You only need to indicate the double quotes in "Quote options".

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

0

Some fields from your archive CSV of origin seem to be em branco.

You need to tell command COPY to interpret these blank fields as if they were NULL, let’s see:

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

Reference: https://www.postgresql.org/docs/9.6/static/sql-copy.html

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.