Re: pg import text data to not null table comma error but semicolon right - Mailing list pgsql-bugs

On Thu, Jun 26, 2014 at 9:24 AM, =E7=8E=8B=E5=AD=A6=E6=95=8F <wang1352083@1=
63.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,
--=20
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br n=C3=ADvel F!
www.dextra.com.br/postgres