Re: How to insert Ecoded values into postrgresql

Dear All,

How to insert encoded data that is(/\_+(at)#$%^&*)(_+) something like
that....I have Csv file .Which contains encoded values.when i try to insert
those. I am getting error..I am not able to insert encoded data.Please
anyone guide me.

I am waiting for your great response.

Thanks and Regards,

Ven

On Wed, Apr 14, 2010 at 6:51 AM, venkat <ven(dot)tammineni(at)gmail(dot)com> wrote:
> Dear All,
>
>    How to insert encoded data that is(/\_+(at)#$%^&*)(_+) something like
> that....I have Csv file .Which contains encoded values.when i try to insert
> those. I am getting error..I am not able to insert encoded data.Please
> anyone guide me.

*) how are you loading your csv file? with copy?

*) is your file properly csv formatted to a particular standard? (such
as rfc4180)

aside: anyone know if postgres properly handles csv according to rfc4180?

merlin

Merlin Moncure wrote:
> On Wed, Apr 14, 2010 at 6:51 AM, venkat <ven(dot)tammineni(at)gmail(dot)com> wrote:
> > Dear All,
> >
> > ?? How to insert encoded data that is(/\_+(at)#$%^&*)(_+) something like
> > that....I have Csv file .Which contains encoded values.when i try to insert
> > those. I am getting error..I am not able to insert encoded data.Please
> > anyone guide me.
>
> *) how are you loading your csv file? with copy?
>
> *) is your file properly csv formatted to a particular standard? (such
> as rfc4180)
>
> aside: anyone know if postgres properly handles csv according to rfc4180?

Wow, I had no idea there was an RFC for CSV. Could you test and tell us
how we compare --- that would be a good thing to document.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Merlin Moncure wrote:
>> aside: anyone know if postgres properly handles csv according to rfc4180?

> Wow, I had no idea there was an RFC for CSV.

Me either. I'd bet the percentage of "CSV"-using programs that actually
conform to the RFC is very small anyway; so while it might be smart to
make sure that what we *emit* follows the RFC, it's probably useless as
a guide to what we need to *accept*.

regards, tom lane

On Thu, Apr 15, 2010 at 12:29 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>> Merlin Moncure wrote:
>>> aside: anyone know if postgres properly handles csv according to rfc4180?
>
>> Wow, I had no idea there was an RFC for CSV.
>
> Me either.  I'd bet the percentage of "CSV"-using programs that actually
> conform to the RFC is very small anyway; so while it might be smart to
> make sure that what we *emit* follows the RFC, it's probably useless as
> a guide to what we need to *accept*.

Well, we would have to accept it if we emit it, but you're right.
Kinda like how with base64 encoding there are two competing
incompatible (both RFC) standards of doing it. We accept both but
only emit one. I'll look at how we handle csv. I've always been
curious about that actually. If a standard exists and it isn't
completely insane maybe we should document and encourage it.

merlin

On 2010-04-14, venkat <ven(dot)tammineni(at)gmail(dot)com> wrote:
> --0016362845f4b36d220484302ab7
> Content-Type: text/plain; charset=ISO-8859-1
>
> Dear All,
>
> How to insert encoded data that is(/\_+(at)#$%^&*)(_+) something like
> that....I have Csv file .Which contains encoded values.when i try to insert
> those. I am getting error..I am not able to insert encoded data.Please
> anyone guide me.

Postgresql can read CSV.

else you probably want pg_escape_string or similar.

On Thu, Apr 15, 2010 at 12:29 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>> Merlin Moncure wrote:
>>> aside: anyone know if postgres properly handles csv according to rfc4180?
>
>> Wow, I had no idea there was an RFC for CSV.
>
> Me either.  I'd bet the percentage of "CSV"-using programs that actually
> conform to the RFC is very small anyway; so while it might be smart to
> make sure that what we *emit* follows the RFC, it's probably useless as
> a guide to what we need to *accept*.

I took a quick look at the rfc. It's very short and appears to codify
common practices. We follow it for both input and output except:

*) trailing spaces are supposed to preserved. we follow sql rules on
trailing spaces which I think makes this point moot.

*) csv is supposed to be CRLF *always*. We do not do this. We do
however read different types of newlines. So we are a 4180 reader but
not an emitter. Not so sure if I think changing this is a good idea
though without exposing a knob.

merlin

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> *) csv is supposed to be CRLF *always*. We do not do this. We do
> however read different types of newlines. So we are a 4180 reader but
> not an emitter. Not so sure if I think changing this is a good idea
> though without exposing a knob.

Given the lack of field complaints, I think changing this would be a
pretty terrible idea; much more likely to break things than fix them.
It's not like that RFC has huge recognition as The Standard.

regards, tom lane

Tom Lane wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> > *) csv is supposed to be CRLF *always*. We do not do this. We do
> > however read different types of newlines. So we are a 4180 reader but
> > not an emitter. Not so sure if I think changing this is a good idea
> > though without exposing a knob.
>
> Given the lack of field complaints, I think changing this would be a
> pretty terrible idea; much more likely to break things than fix them.
> It's not like that RFC has huge recognition as The Standard.

Should we document we support the standard?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Tom Lane wrote:
>> Given the lack of field complaints, I think changing this would be a
>> pretty terrible idea; much more likely to break things than fix them.
>> It's not like that RFC has huge recognition as The Standard.

> Should we document we support the standard?

I'm inclined to think not: there's no evidence that anyone cares about
compliance to that RFC, and anyway we *don't* support the standard
exactly, per Merlin's report. We'd have to say something like "we
support the standard except for line-endings", which is only going to
muddy the waters.

regards, tom lane