Re: pg import text data to not null table comma error but semicolon right

tks
if import data with csv there error
like:

\copy t(d1,d2,d3) from 'comma.csv' with delimiter ',' csv ;
ERROR: null value in column "d2" violates not-null constraint
DETAIL: Failing row contains (e, null, g).

CONTEXT: COPY t, line 2: "e,,g"

and there right
\copy t(d1,d2,d3) from 'comma.csv' with delimiter ',' ;
demo_pg94=>

At 2014-06-26 08:57:32, "Matheus de Oliveira" <matioli(dot)matheus(at)gmail(dot)com> wrote:

On Thu, Jun 26, 2014 at 9:24 AM, 王学敏 <wang1352083(at)163(dot)com> wrote:

\copy t(d1,d2,d3) from 'comma.text' with delimiter ',';
ERROR: missing data for column "d2"
CONTEXT: COPY t, line 1: "a;b;c"

This error happens because the line uses semicolon (";") as delimiter and you used comma (",") on COPY command, so PG thinks "a;b;c" is all value for column d1, and so there is no values for d2 and d3.

Looks like you are using a file different from the one you showed.

\copy t(d1,d2,d3) from 'semicolon.text' with delimiter ';' ;
--no error
cat semicolon.text
a;b;c
e;;f

That should be fine indeed. On the second line d2 will be imported as empty string, not NULL, hence no error.

If you wanted this to be considered NULL, you should be using CSV format, like this:

\copy t(d1,d2,d3) FROM 'semicolon.text' WITH CSV DELIMITER ';' ;

And that would give the error you probably expect:

ERROR: 23502: null value in column "d2" violates not-null constraint
DETAIL: Failing row contains (e, null, f).
CONTEXT: COPY t, line 2: "e;;f"

So. No bug here.

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres