On Wed, Mar 14, 2012 at 7:37 PM, Andy Colson <andy@xxxxxxxxxxxxxxx> wrote: > 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@xxxxxxxxxxxx> 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'; Thought of that one too, but it'll break all the other fields if they have NULLs in them. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
