Postgres Pro
Downloads
Home   >   mailing lists

Thread: Bad Date field

Bad Date field

From
Peralta Miguel-MPERALT1
Date:
Hello everyone:

Would anyone know how to find a bad date field in an external file?  I have
an external file that I am using to populate a table using the following
command:
COPY release_data FROM '/home/mperalta/release_data' USING DELIMITERS '|'

When I issue this command, the response afte a while is the following:

ERROR; Bad date external representation '9'

The external file is rather large and I've tried finding this bad date
manually with no success.  The table I'm copying into has a datestamp of
type DATE.

Any suggestions would be greatly appreciated.

Re: Bad Date field

From
DHSC Webmaster
Date:
You could use a perl script to read your data and do a pattern match for
consistency with your date output. Or you could import the data into a
temp table which has a text field instead of date. You could query
against your temp table to find offending data. Then use SQL to insert
the data into your real table.

Peralta Miguel-MPERALT1 wrote:
>
> Hello everyone:
>
> Would anyone know how to find a bad date field in an external file?  I have
> an external file that I am using to populate a table using the following
> command:
> COPY release_data FROM '/home/mperalta/release_data' USING DELIMITERS '|'
>
> When I issue this command, the response afte a while is the following:
>
> ERROR; Bad date external representation '9'
>
> The external file is rather large and I've tried finding this bad date
> manually with no success.  The table I'm copying into has a datestamp of
> type DATE.
>
> Any suggestions would be greatly appreciated.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Bill MacArthur
Webmaster
DHS Club

Re: Bad Date field

From
bangh
Date:
Suggest:

create another table, use text, instead of date type for that filed. AFter you
get imported and fix invalid data for that filed, in pgsql, you can convert it
to date type

In pgsql, this is easy to find those invalid data.

Bangh

Peralta Miguel-MPERALT1 wrote:

> Hello everyone:
>
> Would anyone know how to find a bad date field in an external file?  I have
> an external file that I am using to populate a table using the following
> command:
> COPY release_data FROM '/home/mperalta/release_data' USING DELIMITERS '|'
>
> When I issue this command, the response afte a while is the following:
>
> ERROR; Bad date external representation '9'
>
> The external file is rather large and I've tried finding this bad date
> manually with no success.  The table I'm copying into has a datestamp of
> type DATE.
>
> Any suggestions would be greatly appreciated.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Bad Date field

From
Tom Lane
Date:
Peralta Miguel-MPERALT1 <Miguel.Peralta@motorola.com> writes:
> COPY release_data FROM '/home/mperalta/release_data' USING DELIMITERS '|'
> When I issue this command, the response afte a while is the following:
> ERROR; Bad date external representation '9'
> The external file is rather large and I've tried finding this bad date
> manually with no success.

How old is your Postgres?  The last several releases include line
numbers when complaining about COPY problems.

In the meantime, I like the other person's suggestion about importing
into a temp table and then working with the temp table to clean up
the data.

            regards, tom lane