Re: Updated COPY CSV patch - Mailing list pgsql-patches

From Andrew Dunstan
Subject Re: Updated COPY CSV patch
Date
Msg-id 407C2BFC.70509@dunslane.net
Whole thread Raw
In response to Re: Updated COPY CSV patch  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-patches
Tom Lane wrote:

>Andrew Dunstan <andrew@dunslane.net> writes:
>
>
>>Tom Lane wrote:
>>
>>
>>>And how would you define "numeric"?
>>>
>>>
>
>
>
>>At least the following:
>>
>>
>
>
>
>> int8
>> int2
>> int4
>> float4
>> float8
>> numeric
>> money
>>
>>
>
>
>
>>and domains based on them.
>>
>>
>
>Wrong answer, as this excludes user-defined types.  COPY should not
>discriminate on the basis of recognizing particular data types.
>
>
>
>>I'm trying to keep this as simple as possible. But we have to be a bit
>>smart if we want to be able to export nicely. Here's the problem: say
>>you have a text field that stores something that has numeric form (phone
>>number, SSN, whatever). You want that exported as text (i.e. quoted).
>>Otherwise, things like leading zeros will get lost by the importing
>>program. However, you *must* not quote genuine number values, or they
>>will not be imported correctly either.
>>
>>
>
>Again, you are trying to make COPY into something it isn't and shouldn't
>be.
>
>
>
>>Exporting nicely has a lot more wrinkles than importing nicely, because
>>predicting the behaviour of the program we might be exporting to is
>>difficult.
>>
>>
>
>s/difficult/impossible/.  I might be willing to accept this sort of
>cruft if it were well-defined cruft, but in point of fact you are trying
>to set up expectations that will be impossible to satisfy.  We will be
>forever making more little tweaks to COPY that render its behavior ever
>less predictable, in the vain hope of reclosing the can of worms you
>want to open.  It would be a lot wiser to implement this sort of
>behavior outside the backend, in code that is easily hacked by users.
>
>
>

I have neither the time nor the inclination for a fight over this. I
hope I never have to use this anyway.

Plan B would be to provide a libpq client for importing/exporting CSVs.
This is such a basic function that I'd like to see it in the core
distribution. Most of the required logic is in what has already been
done.  It would mean that it had to be done via a client connection,
which might have speed implications for very large imports that could
run faster direct from the server's file system.

I thought there was an agreement on how to do this, but the straight
jacket into which it is being forced will significantly limit its
utility, IMNSHO.

cheers

andrew



pgsql-patches by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: Updated COPY CSV patch
Next
From: Andrew Dunstan
Date:
Subject: Re: Updated COPY CSV patch