1

How can I insert attachments to JSONB using PostgreSQL ?

Is any special key, like "_attachments:{}" ? Where Can I find in manual, about inserting files, binary data, attachments ?

InnerWorld
  • 585
  • 7
  • 26

1 Answers1

2

This really has nothing to do with PostgreSQL its self, it's down to the JSON object-serialization format, rather than PostgreSQL's implementation of it.

JSON is a text-based serialization, so you cannot embed binary data in it directly.

You must encode it to a form that's valid encoded text with no null bytes, etc.

Typically you do this by encoding it as base64 or base85.

In PostgreSQL you'll want to use encode(some_bytea, 'base64') and the corresponding decode call. PostgreSQL doesn't have built-in base85 support.

See:

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Is this mean, that attachments in PostgreSQL have not any special meaning like in CouchDB ? What is performance if I would like to store 1MB 10MB or 4GB documents for some reason ? I think, I could reach limit for key value length (if there is any), so 4GB document could exceed this limit ? Also, did this mean, that I should keep attachments as separated documents, for increase search, get, update operation ? – InnerWorld Sep 23 '15 at 10:13
  • @InnerWorld I don't know CouchDB or what it considers an "attachment" so I can't really say. Performance-wise: test and see. It depends on too many factors for a generic answer to make any sense. You're correct that size is an issue; the maximum size of the datum is 1GB, but you'll probably be able to store quite a bit less than 1GB because of base64 encoding. – Craig Ringer Sep 23 '15 at 10:32
  • Also, remember that like any composite field, when you update any part of the field the whole field must be copied due to MVCC. – Craig Ringer Sep 23 '15 at 10:34
  • It sounds like the underlying question here is something like "can I use PostgreSQL with a single table with a single jsonb column like a CouchDB document store". If so, the answer is "not really". Anyway, please post new questions if you want to go into more depth. – Craig Ringer Sep 23 '15 at 10:42