Re: Regarding bytea column in Posgresql

On Thu, 9 Apr 2015 11:03:30 +0000
"Deole, Pushkar (Pushkar)" <pdeole(at)avaya(dot)com> wrote:
>
> I have been assigned to a product that uses Postgresql 9.3 as backend database. I am new to postgresql.
> The product provides chat functionality between the uses and the completed chats are stored in the database table in a 'bytea' column in the form of xml. When I query the data from this column I see xml file with text data. I have couple of queries:
>
> 1. Is 'bytea' column intended for storing text data?

No, it's intended for storing binary data.

> 2. Typically a chat can have text data with several special characters (which can be represented in multi bytes), how these characters can be stored in a bytea column and retrieved back properly?

bytea won't help you here. You'll have to manage the special
characters entirely in your code. bytea gives you back the
exact same types you put in, with no changes or interpretation.

A better choice would be to use a text field with a proper
text encoding (such as utf-8).

Probably an even better choice would be to use the XML datatype
in PostgreSQL, since you say that you're storing XML anyway.

The place where people tend to get tripped up with TEXT and
XML datatypes is that they're strict. If you try to store
text in a TEXT data type that isn't valid (i.e., multi-byte
characters that aren't correct) you'll get an error and the
data won't be accepted. The same thing happens if you try to
store invalid XML in an XML field (such as XML without proper
closing tags, etc). It seems that this strictness causes a lot
of people to avoid those data types, as there seem to be a lot
of people who would rather have garbage data in their database
than actually go to the work of fixing their application.

--
Bill Moran