CODEWITHSUNDEEP
databasepostgresql
Gaurav Dadhania
Gaurav Dadhania

Reputation: 5337

Migrating to PostgreSQL 'xml' datatype

G'day,

I have databases (psql 8.3) that currently consist of "xml" blobs as text type within the database. Now, I've gone through each of these blobs, created a schema which the data should adhere to and made sure the current blobs in the DB validate against that schema. Now, my question is:

  • Should I decide to change the type of these blobs from text to xml, would I encounter any major problems?

  • What advantage would that give me over my current set-up?

    • Thanks!

    Upvotes: 2

    Views: 748

    Answers (2)

    Peter Eisentraut
    Peter Eisentraut

    Reputation: 36749

    This is explained here: http://www.postgresql.org/docs/8.3/static/datatype-xml.html

    Its advantage over storing XML data in a text field is that it checks the input values for well-formedness, and there are support functions to perform type-safe operations on it.

    So I'd say you should use it.

    Upvotes: 0

    user330315
    user330315

    Reputation:

    It depends on how you use that XML data.

    If you always retrieve them from the database as a single value and do all XML processing (e.g. XSLT or extracting information) in your application I would stick with the text data type.

    If you regularily query the XML using PostgreSQL xml functions and need to index those queries, then using a xmltype column is probably better.

    Upvotes: 1

    Related Questions