On Thu, 9 Apr 2015 11:03:30 +0000 "Deole, Pushkar (Pushkar)" <pdeole@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
