Re: URGENT: temporary table not recognized?

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.