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

=E7=8E=8B=E5=AD=A6=E6=95=8F wrote
> Hi,
> when i imort data from csv to table ,there may be some error
> create table t(d1 text not null,d2 text not null,d3 text not null);
>=20
>  \copy t(d1,d2,d3) from 'comma.text' with  delimiter ',';             =20
> ERROR:  missing data for column "d2"
> CONTEXT:  COPY t, line 1: "a;b;c"
>=20
>  \copy t(d1,d2,d3) from 'semicolon.text' with  delimiter ';' ;
> --no error
> cat comma.text
> a,b,c
> e,,f
>=20
> cat semicolon.text
> a;b;c
> e;;f

Apparently the comma.text file you are trying to import and the one you are
cat'ing are not the same file...

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/pg-im=
port-text-data-to-not-null-table-comma-error-but-semicolon-right-tp5809343p=
5809346.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

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

=E7=8E=8B=E5=AD=A6=E6=95=8F wrote
> tks
> if import data with csv there error
> like:
>=20
>  \copy t(d1,d2,d3) from 'comma.csv' with  delimiter ',' csv ;      =20
> ERROR:  null value in column "d2" violates not-null constraint
> DETAIL:  Failing row contains (e, null, g).
>=20
> CONTEXT:  COPY t, line 2: "e,,g"
>=20
> and there  right
>  \copy t(d1,d2,d3) from 'comma.csv' with  delimiter ','  ;  =20
> demo_pg94=3D>

In CSV mode the empty-string is converted into NULL whereas in non-CSV mode
the empty string is left as an empty string.

http://www.postgresql.org/docs/9.3/interactive/sql-copy.html

see "NULL"

You are only getting an error in the first case because of the definition o=
f
t.d2 includes a NOT NULL constraint.

You can either remove the constraint or modify the \copy command to not
treat empty strings as NULL is CSV mode.  It kinda depends on the real data
which option is better.  Typically I'd drop NOT NULL and treat the copy
target as a staging table; then when migrating to the live table I'd
COALESCE the NULL into a meaningful default.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/pg-im=
port-text-data-to-not-null-table-comma-error-but-semicolon-right-tp5809343p=
5809539.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.