Which postgres data type should I use for large XML message come from network?

Question

I have incoming xml which is potentially as big as 5M and I need to store it it with postgres 9.1. Which data type should I use ?

bytea 
character varying
text

or something else ?

BTW The xml itself contains some binary data in base64 format, does that make any difference when choosing data type in postgres ?

Thank

xml maybe? Definitely not bytea as that is for binary data, varchar and text are effectively the same thing in Postgres (as documented in the manual). — user330315
– user330315, Commented May 18, 2013 at 15:36
The xml itself contains some binary data encoded in base64 format, does that make any difference? — RoundPi
– RoundPi, Commented May 18, 2013 at 15:48
XML is text (strings) you cannot have "real" binary data in it. Everything in there is represented as a String. — user330315
– user330315, Commented May 18, 2013 at 15:50
But the fact the String can be as big as 5M in size would it be storing bytea have certain advantage ? Can xml or char varying cope with 5M long string effectively (load and save)? — RoundPi
– RoundPi, Commented May 18, 2013 at 16:02

Ihor Romanchenko · Accepted Answer · 2013-05-18 18:41:14Z

6

You have two options:

VARCHAR or TEXT. It will allow you to store and retrieve your XML file from DB. But nothing more.
XML . Will allow you to store, retrieve, validate, edit, search ... the XML files, but may (or may not) involve some overhead on storing files to DB.

There is no reason to store XML files as BYTEA at all. 5 MB size is nothing special for Postgres. In my last project I worked strings with up to 0.5 GB length in Postgres.

answered May 18, 2013 at 18:41

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RoundPi Over a year ago

Thanks for your answer and you stored the 0.5G length string with Text or VarChar and works fine?

Ihor Romanchenko Over a year ago

@Gob00st There were some problems on driver level, when storing 100+ MB varchar to DB. There were no problems with 5-10 MB strings.

RoundPi Over a year ago

Thanks mine is 5 upper limit so should be fine.

Collectives™ on Stack Overflow

Which postgres data type should I use for large XML message come from network?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related