On 01/06/2012 03:42 PM, Phoenix Kiula wrote:
> On Fri, Jan 6, 2012 at 6:20 PM, Adrian Klaver<adrian.klaver@gmail.com> wrote:
>
>> http://www.postgresql.org/docs/9.0/interactive/sql-copy.html
>>
>> Search for
>> NULL
>
>
> Thanks Adrian.
>
> Without examples, it's hard to predict syntax. If the value after a
> pipe is missing altogether, I suppose the missing value is "\n"
> (newline). But this doesn't work:
>
> copy vl from 'data.txt' WITH DELIMITER AS '|' NULL '\n';
>
> None of these work either:
>
> copy vl from 'data.txt' WITH DELIMITER AS '|' NULL \n;
> copy vl from 'data.txt' WITH DELIMITER AS '|' NULL \\n;
> copy vl from 'data.txt' WITH DELIMITER AS '|' NULL '';
>
> The first two give errors, the third one throws the same missing value
> for column error.
>
> The data is stored like this:
>
> 123|big string here|189209209|US|2001-01-01
> 123|big string here|189209209|US|2001-01-01
> 123|big string here|189209209|US|2001-01-01
> 123|big string here|189209209|US|2001-01-01
>
> But sometimes, the strings are:
>
> |big string here|189209209|US|2001-01-01
> |big string here|189209209|US|2001-01-01
>
> Or
>
> 123|big string here|189209209|US
> 123|big string here|189209209|US|
>
> So you see either the first column, which is the ID in a way, is
> missing so the "missing character" is probably a blank (''?). In this
> case I want COPY to just ignore this line.
>
> Or the last column is missing, where the missing character can be a
> newline I suppose?
>
> So how do I specify this in the COPY command so that it doesn't croak?
> If a line's ID is missing, it should ignore the line and go on instead
> of not doing anything by throwing an error for EVERYTHING!
>
> Thanks.
>
>
Missing data is one thing, missing delimiters is another. Try doing a
small copy of data with just a few lines to see which variants are
actually causing the error. My money is on the one that has a mismatch
between the table column count and the data column count. I.e., the row
with three delimiters instead of four:
23|big string here|189209209|US
When you say "ignore", do you mean that you want PostgreSQL to assume a
null value for the missing column or to not import that row at all?
In general, when you have data scrubbing issues like this,
grep/sed/awk/... are your friends. Clean it up then import it.
I suppose you could import all rows into a big text field and process it
in PostgreSQL but I doubt you will find that to be an optimal solution.
Cheers,
Steve