Thread: Support UTF-8 files with BOM in COPY FROM

Support UTF-8 files with BOM in COPY FROM

From
Itagaki Takahiro
Date:
Hi,

I'd like to support UTF-8 text or csv files that has BOM (byte order mark)
in COPY FROM command. BOM will be automatically detected and ignored
if the file encoding is UTF-8. WIP patch attached.

I'm thinking about only COPY FROM for reads, but if someone wants to add
BOM in COPY TO, we might also support COPY TO WITH BOM for writes.

Comments welcome.

--
Itagaki Takahiro

Attachment

Re: Support UTF-8 files with BOM in COPY FROM

From
"David E. Wheeler"
Date:
On Sep 25, 2011, at 9:58 PM, Itagaki Takahiro wrote:

> I'd like to support UTF-8 text or csv files that has BOM (byte order mark)
> in COPY FROM command. BOM will be automatically detected and ignored
> if the file encoding is UTF-8. WIP patch attached.

By my reading of http://unicode.org/faq/utf_bom.html#bom5, I'd say +1

So I think what you propose makes sense.

> I'm thinking about only COPY FROM for reads, but if someone wants to add
> BOM in COPY TO, we might also support COPY TO WITH BOM for writes.

I think it would have to be optional, since "some recipients of UTF-8 encoded data do not expect a BOM."

Best,

David



Re: Support UTF-8 files with BOM in COPY FROM

From
Magnus Hagander
Date:
On Mon, Sep 26, 2011 at 06:58, Itagaki Takahiro
<itagaki.takahiro@gmail.com> wrote:
> Hi,
>
> I'd like to support UTF-8 text or csv files that has BOM (byte order mark)
> in COPY FROM command. BOM will be automatically detected and ignored
> if the file encoding is UTF-8. WIP patch attached.
>
> I'm thinking about only COPY FROM for reads, but if someone wants to add
> BOM in COPY TO, we might also support COPY TO WITH BOM for writes.
>
> Comments welcome.

I like it in general. But if we're looking at the BOM, shouldn't we
also look and *reject* the file if it's a BOM for a non-UTF8 file? Say
if the BOM claims it's UTF16?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


Re: Support UTF-8 files with BOM in COPY FROM

From
Itagaki Takahiro
Date:
On Mon, Sep 26, 2011 at 20:12, Magnus Hagander <magnus@hagander.net> wrote:
> I like it in general. But if we're looking at the BOM, shouldn't we
> also look and *reject* the file if it's a BOM for a non-UTF8 file? Say
> if the BOM claims it's UTF16?

-1 because we're depending on manual configuration for now.
It would be reasonable if we had used automatic detection of
character encoding, but we don't. In addition, some crazy
encoding might use BOM codes as a valid character.

-- 
Itagaki Takahiro


Re: Support UTF-8 files with BOM in COPY FROM

From
Magnus Hagander
Date:
On Mon, Sep 26, 2011 at 13:36, Itagaki Takahiro
<itagaki.takahiro@gmail.com> wrote:
> On Mon, Sep 26, 2011 at 20:12, Magnus Hagander <magnus@hagander.net> wrote:
>> I like it in general. But if we're looking at the BOM, shouldn't we
>> also look and *reject* the file if it's a BOM for a non-UTF8 file? Say
>> if the BOM claims it's UTF16?
>
> -1 because we're depending on manual configuration for now.
> It would be reasonable if we had used automatic detection of
> character encoding, but we don't. In addition, some crazy
> encoding might use BOM codes as a valid character.

Does such an encoding really exist? And the code only executes when
the user thinks he's in UTF8, right? So it would still only happen if
the incorrect encoding was specified..


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


Re: Support UTF-8 files with BOM in COPY FROM

From
Andrew Dunstan
Date:

On 09/26/2011 07:12 AM, Magnus Hagander wrote:
> On Mon, Sep 26, 2011 at 06:58, Itagaki Takahiro
> <itagaki.takahiro@gmail.com>  wrote:
>> Hi,
>>
>> I'd like to support UTF-8 text or csv files that has BOM (byte order mark)
>> in COPY FROM command. BOM will be automatically detected and ignored
>> if the file encoding is UTF-8. WIP patch attached.
>>
>> I'm thinking about only COPY FROM for reads, but if someone wants to add
>> BOM in COPY TO, we might also support COPY TO WITH BOM for writes.
>>
>> Comments welcome.
> I like it in general. But if we're looking at the BOM, shouldn't we
> also look and *reject* the file if it's a BOM for a non-UTF8 file? Say
> if the BOM claims it's UTF16?
>


It should be rejected as invalidly encoded anyway, as a non-utf8 BOM is 
not valid utf-8. We shouldn't check in non-unicode cases where the 
sequence might be valid in those encodings (e.g. ISO-8859-1).

cheers

andrew


Re: Support UTF-8 files with BOM in COPY FROM

From
Tom Lane
Date:
"David E. Wheeler" <david@kineticode.com> <CAJW2+qdYg1+xLaHDqnJs3AcKmCSVCDkv_LCAPWUtwmxL9dzVhQ@mail.gmail.com> writes:
> On Sep 25, 2011, at 9:58 PM, Itagaki Takahiro wrote:
>> I'm thinking about only COPY FROM for reads, but if someone wants to add
>> BOM in COPY TO, we might also support COPY TO WITH BOM for writes.

> I think it would have to be optional, since "some recipients of UTF-8 encoded data do not expect a BOM."

Putting a BOM into UTF8 data is flat out invalid per spec --- the fact
that Microsloth does it does not make it standards-conformant.

I think that accepting it on input can be sensible, on the principle of
"be liberal in what you accept", but the other side of that is "be
conservative in what you send".  No BOMs in output, please.
        regards, tom lane


Re: Support UTF-8 files with BOM in COPY FROM

From
Tatsuo Ishii
Date:
> I'd like to support UTF-8 text or csv files that has BOM (byte order mark)
> in COPY FROM command. BOM will be automatically detected and ignored
> if the file encoding is UTF-8. WIP patch attached.

From RFC3629(http://tools.ietf.org/html/rfc3629#section-6):
o A protocol SHOULD forbid use of U+FEFF as a signature for those  textual protocol elements that the protocol mandates
tobe always  UTF-8, the signature function being totally useless in those cases.
 

COPY explicitly specifies the encoding (to be UTF-8 in this case).  So
I think we should not regard U+FEFF as "BOM" in COPY, rather we should
regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE".
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp


Re: Support UTF-8 files with BOM in COPY FROM

From
Tatsuo Ishii
Date:
> "David E. Wheeler" <david@kineticode.com> <CAJW2+qdYg1+xLaHDqnJs3AcKmCSVCDkv_LCAPWUtwmxL9dzVhQ@mail.gmail.com>
writes:
>> On Sep 25, 2011, at 9:58 PM, Itagaki Takahiro wrote:
>>> I'm thinking about only COPY FROM for reads, but if someone wants to add
>>> BOM in COPY TO, we might also support COPY TO WITH BOM for writes.
> 
>> I think it would have to be optional, since "some recipients of UTF-8 encoded data do not expect a BOM."
> 
> Putting a BOM into UTF8 data is flat out invalid per spec --- the fact
> that Microsloth does it does not make it standards-conformant.
> 
> I think that accepting it on input can be sensible, on the principle of
> "be liberal in what you accept", but the other side of that is "be
> conservative in what you send".  No BOMs in output, please.

Suppose a user uses brain-dead editor, which does not accept UTF-8
without BOM.  He decides to save his editor data into PostgreSQL using
COPY FROM. He extracts the data using COPY TO. Now he finds that his
stupid editor does not accept his data any more.

So I think if we decide to accept UTF-8 with BOM, we should keep BOM
when importing the data and output the data with BOM. If we don't want
to output UTF-8 with BOM, we should not accept UTF-8 with BOM. It
seems we don't have much choice...
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp


Re: Support UTF-8 files with BOM in COPY FROM

From
Robert Haas
Date:
On Mon, Sep 26, 2011 at 11:09 AM, Tatsuo Ishii <ishii@postgresql.org> wrote:
>> "David E. Wheeler" <david@kineticode.com> <CAJW2+qdYg1+xLaHDqnJs3AcKmCSVCDkv_LCAPWUtwmxL9dzVhQ@mail.gmail.com>
writes:
>>> On Sep 25, 2011, at 9:58 PM, Itagaki Takahiro wrote:
>>>> I'm thinking about only COPY FROM for reads, but if someone wants to add
>>>> BOM in COPY TO, we might also support COPY TO WITH BOM for writes.
>>
>>> I think it would have to be optional, since "some recipients of UTF-8 encoded data do not expect a BOM."
>>
>> Putting a BOM into UTF8 data is flat out invalid per spec --- the fact
>> that Microsloth does it does not make it standards-conformant.
>>
>> I think that accepting it on input can be sensible, on the principle of
>> "be liberal in what you accept", but the other side of that is "be
>> conservative in what you send".  No BOMs in output, please.
>
> Suppose a user uses brain-dead editor, which does not accept UTF-8
> without BOM.  He decides to save his editor data into PostgreSQL using
> COPY FROM. He extracts the data using COPY TO. Now he finds that his
> stupid editor does not accept his data any more.
>
> So I think if we decide to accept UTF-8 with BOM, we should keep BOM
> when importing the data and output the data with BOM. If we don't want
> to output UTF-8 with BOM, we should not accept UTF-8 with BOM. It
> seems we don't have much choice...

Maybe this needs to be an optional behavior, controlled by some COPY option.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Support UTF-8 files with BOM in COPY FROM

From
Robert Haas
Date:
On Mon, Sep 26, 2011 at 1:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Mon, Sep 26, 2011 at 11:09 AM, Tatsuo Ishii <ishii@postgresql.org> wrote:
>>> Suppose a user uses brain-dead editor, which does not accept UTF-8
>>> without BOM.
>
>> Maybe this needs to be an optional behavior, controlled by some COPY option.
>
> I'm not excited about emitting non-standards-conformant output on the
> strength of a hypothetical argument about users and editors that may or
> may not exist.  I believe that there's a use-case for reading BOMs, but
> I have seen no field complaints demonstrating that we need to write
> them.  Even if we had a couple, "use a less brain dead editor" might be
> the best response.  We cannot promise to be compatible with arbitrarily
> broken software.

The thing that makes me doubt that is this comment from Tatsuo Ishii:

TI> COPY explicitly specifies the encoding (to be UTF-8 in this case).  So
TI> I think we should not regard U+FEFF as "BOM" in COPY, rather we should
TI> regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE".

If a BOM is confusable with valid data, then I think recognizing it
and discarding it unconditionally is no good - you could end up where
COPY OUT, TRUNCATE, COPY IN changes the table contents.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Support UTF-8 files with BOM in COPY FROM

From
Tom Lane
Date:
Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Sep 26, 2011 at 11:09 AM, Tatsuo Ishii <ishii@postgresql.org> wrote:
>> Suppose a user uses brain-dead editor, which does not accept UTF-8
>> without BOM.

> Maybe this needs to be an optional behavior, controlled by some COPY option.

I'm not excited about emitting non-standards-conformant output on the
strength of a hypothetical argument about users and editors that may or
may not exist.  I believe that there's a use-case for reading BOMs, but
I have seen no field complaints demonstrating that we need to write
them.  Even if we had a couple, "use a less brain dead editor" might be
the best response.  We cannot promise to be compatible with arbitrarily
broken software.
        regards, tom lane


Re: Support UTF-8 files with BOM in COPY FROM

From
Tom Lane
Date:
Robert Haas <robertmhaas@gmail.com> writes:
> The thing that makes me doubt that is this comment from Tatsuo Ishii:
> TI> COPY explicitly specifies the encoding (to be UTF-8 in this case).  So
> TI> I think we should not regard U+FEFF as "BOM" in COPY, rather we should
> TI> regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE".

Yeah, that's a reasonable argument for rejecting the patch altogether.
I'm not qualified to decide whether it outweighs the "we need to be able
to read Notepad output" argument.  I do observe that
http://en.wikipedia.org/wiki/Byte_order_mark
says Unicode 3.2 has deprecated the no-break-space interpretation,
but on the other hand you're right that we can't really assume that
the character is not present in people's data.
        regards, tom lane


Re: Support UTF-8 files with BOM in COPY FROM

From
Robert Haas
Date:
On Mon, Sep 26, 2011 at 1:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> The thing that makes me doubt that is this comment from Tatsuo Ishii:
>> TI> COPY explicitly specifies the encoding (to be UTF-8 in this case).  So
>> TI> I think we should not regard U+FEFF as "BOM" in COPY, rather we should
>> TI> regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE".
>
> Yeah, that's a reasonable argument for rejecting the patch altogether.

Yeah, or for making the behavior optional.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Support UTF-8 files with BOM in COPY FROM

From
Tom Lane
Date:
Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Sep 26, 2011 at 1:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Robert Haas <robertmhaas@gmail.com> writes:
>>> The thing that makes me doubt that is this comment from Tatsuo Ishii:
>>> TI> COPY explicitly specifies the encoding (to be UTF-8 in this case). �So
>>> TI> I think we should not regard U+FEFF as "BOM" in COPY, rather we should
>>> TI> regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE".

>> Yeah, that's a reasonable argument for rejecting the patch altogether.

> Yeah, or for making the behavior optional.

Sorry, I should have been clearer: it's an argument for rejecting *this*
patch.  A patch that introduced a "BOM" option for COPY (which logically
could apply just as well to input or output) would be a different patch.

BTW, another issue with the patch-as-proposed is that it assumes,
without even checking, that fseek() will work (for that matter, it would
also fail pretty miserably on a file shorter than 3 bytes).  We could
dodge that problem with an option since it would be reasonable to define
the option as meaning that there MUST be a BOM there.  I would envision
it as acting much like the CSV HEADER option.
        regards, tom lane


Re: Support UTF-8 files with BOM in COPY FROM

From
Peter Eisentraut
Date:
On tis, 2011-09-27 at 00:09 +0900, Tatsuo Ishii wrote:
> Suppose a user uses brain-dead editor, which does not accept UTF-8
> without BOM.

I would first like to see evidence that such an editor exists.



Re: Support UTF-8 files with BOM in COPY FROM

From
Peter Eisentraut
Date:
On mån, 2011-09-26 at 13:19 -0400, Robert Haas wrote:
> The thing that makes me doubt that is this comment from Tatsuo Ishii:
> 
> TI> COPY explicitly specifies the encoding (to be UTF-8 in this case).
> So
> TI> I think we should not regard U+FEFF as "BOM" in COPY, rather we
> should
> TI> regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE".
> 
> If a BOM is confusable with valid data, then I think recognizing it
> and discarding it unconditionally is no good - you could end up where
> COPY OUT, TRUNCATE, COPY IN changes the table contents.

We did recently accept a patch for psql -f to skip over a UTF-8
byte-order mark.  We had a lot of this same discussion there.



Re: Support UTF-8 files with BOM in COPY FROM

From
Robert Haas
Date:
On Mon, Sep 26, 2011 at 2:38 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> On mån, 2011-09-26 at 13:19 -0400, Robert Haas wrote:
>> The thing that makes me doubt that is this comment from Tatsuo Ishii:
>>
>> TI> COPY explicitly specifies the encoding (to be UTF-8 in this case).
>> So
>> TI> I think we should not regard U+FEFF as "BOM" in COPY, rather we
>> should
>> TI> regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE".
>>
>> If a BOM is confusable with valid data, then I think recognizing it
>> and discarding it unconditionally is no good - you could end up where
>> COPY OUT, TRUNCATE, COPY IN changes the table contents.
>
> We did recently accept a patch for psql -f to skip over a UTF-8
> byte-order mark.  We had a lot of this same discussion there.

But that case is different, because zero-width, non-breaking space has
no particular meaning in an SQL script - it's either going to be
ignored as a BOM, ignored as whitespace, or an error.  But inside a
file being subjected to COPY it might be confusable with data that the
user wanted to end up in some table.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Support UTF-8 files with BOM in COPY FROM

From
Andrew Dunstan
Date:

On 09/26/2011 02:38 PM, Peter Eisentraut wrote:
> On mån, 2011-09-26 at 13:19 -0400, Robert Haas wrote:
>> The thing that makes me doubt that is this comment from Tatsuo Ishii:
>>
>> TI>  COPY explicitly specifies the encoding (to be UTF-8 in this case).
>> So
>> TI>  I think we should not regard U+FEFF as "BOM" in COPY, rather we
>> should
>> TI>  regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE".
>>
>> If a BOM is confusable with valid data, then I think recognizing it
>> and discarding it unconditionally is no good - you could end up where
>> COPY OUT, TRUNCATE, COPY IN changes the table contents.
> We did recently accept a patch for psql -f to skip over a UTF-8
> byte-order mark.  We had a lot of this same discussion there.
>
>

Yes, but wasn't part of the rationale that this was safe because a 
leading BOM could not possibly be mistaken for anything else legitimate 
in an SQL source file? That's quite different from a data file. ISTM.

cheers

andrew


Re: Support UTF-8 files with BOM in COPY FROM

From
Peter Eisentraut
Date:
On mån, 2011-09-26 at 14:44 -0400, Robert Haas wrote:
> > We did recently accept a patch for psql -f to skip over a UTF-8
> > byte-order mark.  We had a lot of this same discussion there.
> 
> But that case is different, because zero-width, non-breaking space has
> no particular meaning in an SQL script - it's either going to be
> ignored as a BOM, ignored as whitespace, or an error.  But inside a
> file being subjected to COPY it might be confusable with data that the
> user wanted to end up in some table.

Yes, my point was more directed toward the discussion about whether BOM
in UTF-8 are valid at all.  But your point pretty much kills this
altogether.  If I store a BOM in row 1, column 1 of my table, because,
well, maybe it's an XML document or something, then it needs to be able
to survive a copy out and in.  The only way we could proceed with this
would be if we prohibited BOMs in all user-data.




Re: Support UTF-8 files with BOM in COPY FROM

From
Brar Piening
Date:
Tom Lane wrote:
> Putting a BOM into UTF8 data is flat out invalid per spec --- the fact
> that Microsloth does it does not make it standards-conformant.

Could you share a pointer to the spec?
All I've ever heard is that a BOM is optional for UTF-8 but not forbidden.

The Unicode FAQ (http://unicode.org/faq/utf_bom.html#BOM) states "that 
some recipients of UTF-8 encoded data do not expect a BOM".
Postgres obviously belongs to those recipients.
That's why all my psql-scripts transferring data from MSSQL to Postgres 
need a '\! perl -CD -pi.orig -e "tr/\x{feff}//d" "C:/datafile.txt"' 
before feeding data into COPY TO.

Reading it tolerantly and writing it on user request is probably the way 
that would help most users.

Regards,

Brar



Re: Support UTF-8 files with BOM in COPY FROM

From
Brar Piening
Date:
Tom Lane wrote:
> Yeah, that's a reasonable argument for rejecting the patch altogether.
> I'm not qualified to decide whether it outweighs the "we need to be able
> to read Notepad output" argument.

Actually it's not only notepad.

I quite often find myself doing something like the following when moving 
data from MSSQL to PostgreSQL.

\echo Fetching data for table "patient"
\! sqlcmd -S DBSERVER -d DATABASE -E -f 65001 -o "C:/datafile.txt" -h -1 
-W -s "|" -Q "SET NOCOUNT ON; SELECT * FROM my_table;"
\! perl -CD -pi.orig -e "tr/\x{feff}//d" "C:/datafile.txt"

\echo Importing data into table "patient"
\copy my_table FROM 'C:/datafile.txt' WITH DELIMITER '|' NULL 'NULL'

Regards,

Brar


Re: Support UTF-8 files with BOM in COPY FROM

From
Brar Piening
Date:
Robert Haas wrote:
> The thing that makes me doubt that is this comment from Tatsuo Ishii:
>
> TI>  COPY explicitly specifies the encoding (to be UTF-8 in this case).  So
> TI>  I think we should not regard U+FEFF as "BOM" in COPY, rather we should
> TI>  regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE".
>
> If a BOM is confusable with valid data, then I think recognizing it
> and discarding it unconditionally is no good - you could end up where
> COPY OUT, TRUNCATE, COPY IN changes the table contents.

Citing from the Unicode FAQ again:

Q: Where is a BOM useful?
A: A BOM is useful at the beginning of files that are typed as text, but 
for which it is not known whether they are in big or little endian 
format—it can also serve as a hint indicating that the file is in 
Unicode, as opposed to in a legacy encoding and furthermore, it act as a 
signature for the specific encoding form used.

I think that the major hint in the answer is "beginning of files".

To correctly handle a BOM you need to be sure to be in the context of 
files that have defined bounds (especially a *beginning*) you can't 
properly handle a BOM in arbitrary streams.

Regards,

Brar


Re: Support UTF-8 files with BOM in COPY FROM

From
Tom Lane
Date:
Brar Piening <brar@gmx.de> writes:
> Citing from the Unicode FAQ again:

> Q: Where is a BOM useful?
> A: A BOM is useful at the beginning of files that are typed as text, but 
> for which it is not known whether they are in big or little endian 
> format�it can also serve as a hint indicating that the file is in 
> Unicode, as opposed to in a legacy encoding and furthermore, it act as a 
> signature for the specific encoding form used.

Note that the reference to byte order betrays the implicit context
assumption: that we're talking about UTF16 or UTF32 representation.
A BOM in UTF8 data is useless for its intended purpose of disambiguating
byte order.  It could possibly be useful for telling UTF8 data apart
from non-UTF8 data, except for the inconvenient fact that that byte
sequence is not invalid data in non-UTF8 encodings.

BOM is useless in UTF8, no matter what Microsoft thinks.  Any tool that
relies on it to detect UTF8 data has to have a workaround for overriding
that detection, or it's broken to the point of uselessness.
        regards, tom lane


Re: Support UTF-8 files with BOM in COPY FROM

From
Brar Piening
Date:
<span id="IDstID">Tom Lane wrote:</span><blockquote cite="mid:29877.1317066533@sss.pgh.pa.us" type="cite"><pre
wrap="">
Note that the reference to byte order betrays the implicit context
assumption: that we're talking about UTF16 or UTF32 representation.</pre></blockquote> Note that there is no implicit
contextassumption in the Unicode FAQ. It's equally covering UTF-8, UTF-16 and UTF-32.<br /> Another quote:<br /> Q: Can
aUTF-8 data stream contain the BOM character (in UTF-8 form)? If yes, then can I still assume the remaining UTF-8 bytes
arein big-endian order?<br /> A: Yes, UTF-8 can contain a BOM. However, it makes <i>no</i> difference as to the
endiannessof the byte stream. UTF-8 always has the same byte order. An initial BOM is <i>only</i> used as a signature —
anindication that an otherwise unmarked text file is in UTF-8. Note that some recipients of UTF-8 encoded data do not
expecta BOM. Where UTF-8 is used<i> transparently</i> in 8-bit environments, the use of a BOM will interfere with any
protocolor file format that expects specific ASCII characters at the beginning, such as the use of "#!" of at the
beginningof Unix shell scripts.<br /><blockquote cite="mid:29877.1317066533@sss.pgh.pa.us" type="cite"><pre wrap="">
 

BOM is useless in UTF8, no matter what Microsoft thinks.  Any tool that
relies on it to detect UTF8 data has to have a workaround for overriding
that detection, or it's broken to the point of uselessness.</pre></blockquote> This kind of brokenness is currently
existingthe other way around (see my reference to the perl script I' using to work aound it).<br /><br /> Note also
thatI'm not citing a Microsoft FAQ but the Unicode FAQ.<br /> I'm also not trying to convert Postgres into a Microsoft
tool(I'm pretty happy it isn't) but I'm pointing to existing compatibility issues on a Platform that others have
decidedto support.<br /> Belonging to the huge group of users who have little or no choice in what OS they are using
andbeing from a country where plain ASCII isn't enough to cover all existing characters this is probably fair.<br /><br
/>It's a pity that the Unicode standard actually allows something that can cause problems but blaming the non-platform
againdoesn't solve the existing issues.<br /><br /> Regards,<br /><br /> Brar<br /> 

Re: Support UTF-8 files with BOM in COPY FROM

From
Brar Piening
Date:
Brar Piening wrote:
>
> It's a pity that the Unicode standard actually allows something that 
> can cause problems but blaming the non-platform again doesn't solve 
> the existing issues.

To put in a more humoruos but actually correct way:

M$ has found a standard conforming way of preventing users to migrate 
data from MSSQL to PostgreSQL.
Do you want to work arond it?

Regards,

Brar


Re: Support UTF-8 files with BOM in COPY FROM

From
Peter Eisentraut
Date:
On mån, 2011-09-26 at 21:49 +0300, Peter Eisentraut wrote:
> If I store a BOM in row 1, column 1 of my table, because,
> well, maybe it's an XML document or something, then it needs to be
> able to survive a copy out and in.  The only way we could proceed with
> this would be if we prohibited BOMs in all user-data. 

Alternative consideration: We could allow this in CSV format if we made
users quote the first value if it starts with a BOM.  This might be a
reasonable way to get MS compatibility.



Re: Support UTF-8 files with BOM in COPY FROM

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> Alternative consideration: We could allow this in CSV format if we made
> users quote the first value if it starts with a BOM.  This might be a
> reasonable way to get MS compatibility.

I don't think we can get away with a retroactive restriction on the
contents of data files.

If we're going to do this at all, I still think an explicit BOM option
for COPY, to either eat (and require) a BOM on input or emit a BOM on
output, would be the sanest way.  None of the "automatic" approaches
seem safe to me.
        regards, tom lane