Re: [GENERAL] Regarding bytea column in Posgresql
On 4/9/2015 4:10 AM, Bill Moran wrote: 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). one possible rationale for using BYTEA is that the data could be in various encodings, which the application wishes to preserve, and keeps track of somewhere else (perhaps in a field within the XML?). PostgreSQL text types would insist that all text be stored in the same encoding, and anything in a different encoding would have to be converted to the database encoding. Me, I'd be inclined to convert everything to UTF8 and store it as such, and convert it back to the user's encoding on display, but the feasibility of this really depends on the use cases. -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Regarding bytea column in Posgresql
On Thu, 9 Apr 2015 11:03:30 + "Deole, Pushkar (Pushkar)" 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Regarding bytea column in Posgresql
Hi, 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? 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? Thanks, Pushkar
