Re: copy in date string "00-00-00 00:00:00" - Mailing list pgsql-general

From Andy Colson
Subject Re: copy in date string "00-00-00 00:00:00"
Date
Msg-id 4F6147ED.30505@squeakycode.net
Whole thread Raw
In response to Re: copy in date string "00-00-00 00:00:00"  (Andy Colson <andy@squeakycode.net>)
Responses Re: copy in date string "00-00-00 00:00:00"  (Scott Marlowe <scott.marlowe@gmail.com>)
List pgsql-general
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.Phillips@mophilly.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
"copytable 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
timecomponent 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,
eachform 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

pgsql-general by date:

Previous
From: Andy Colson
Date:
Subject: Re: copy in date string "00-00-00 00:00:00"
Next
From: Scott Marlowe
Date:
Subject: Re: copy in date string "00-00-00 00:00:00"