Re: datestyle question

On Sep 26, 2007, at 5:24 PM, Scott Marlowe wrote:

> On 9/26/07, Diego Gil <diego(at)adminsa(dot)com> wrote:
>> Hi,
>>
>> I have a file to import to postgresql that have an unusual date
>> format.
>> For example, Jan 20 2007 is 20022007, in DDMMYYYY format, without any
>> separator. I know that a 20072002 (YYYYMMDD) is ok, but I don't
>> know how
>> to handle the DDMMYYYY dates.
>>
>> I tried and tried but I can't import those dates to postgresql.
>>
>> Any hint, other than editing file ?
>
> There are two approaches. One is to use something like sed or awk or
> perl or php to read the file and rearrange those bits to a format that
> makes sense to pgsql, or you can import that field into a text field,
> and use something like substring() in postgresql to update a new field
> that holds dates with the right numbers.

You know, this type of request is fairly common and has got me
thinking. If postgres had some kind of identity function a useful
extension to the COPY syntax would be to allow the user to specify
functions for each column that the imported data would be passed
through.

So, say you had the following table:

CREATE TABLE test (
test_id serial primary key,
test_val text,
test_date timestamp);

The COPY could be something like (with id being a built in identity
function):

COPY test (test_val, test_date) VALUES (id, regexp_replace(id, '(..)
(..)(....)', '\\3-\\2-\\1') FROM '/somepath/somefile.csv' CSV;

Alternatively, if the usage of id is obtuse, the particular field
name could be used but I think that would probably work a little
differently on the backend although not being involved with the
backend I'm no expert.

Just a random idea anyway.

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com