Re: copy in date string "00-00-00 00:00:00"

On 03/14/2012 08:32 PM, Andy Colson wrote:
> On 03/14/2012 08:16 PM, Scott Marlowe wrote:
>> On Wed, Mar 14, 2012 at 6:47 PM, Mark Phillips
>> <Mark(dot)Phillips(at)mophilly(dot)com> wrote:
>>> I am migrating a data set from Oracle 8i to PG 9.1. The process is to export data into csv files, then use the pg "copy table from file csv header" statement to load the tables.
>>>
>>> There are a number of date columns in the tables that include empty value (null), valid dates, and some with the time component only. The empty values are being output as 00-00-00 00:00:00.
>>>
>>> The import is falling over on rows that contain these "zero" dates.
>>>
>>> I can adjust the NLS session format of the date string, within a small range, in the oracle environment. However, each form I have attempted still results in these "zero" date values in the csv file.
>>>
>>> I am thinking of run the csv files through a filter to change the "00-00-00 00:00:00" to an empty value.
>>>
>>> Is there a way for postgres to handle this?
>>
>> Can you run it through sed and replace the "0000-00-00 00:00:00" to
>> NULL (no quotes) ? That should work.
>>
>
> I think COPY (depending on arguments) uses \N by default.
>
> Another option is to pull it into a temp table and make fix it up from there.
>
> -Andy
>

humm.. and speaking of arguments, Mark, did you check the help?

where option can be one of:

NULL 'null_string'

so, perhaps just:

COPY tbl from 'dump.csv' with NULL '00-00-00 00:00:00';

-Andy