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

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