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

王学敏 wrote
> 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=>

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 of
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-import-text-data-to-not-null-table-comma-error-but-semicolon-right-tp5809343p5809539.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.