0

I want to write a query for a table like this:

id  job_id  data (bytea)
--------------------------
 1       1  {"hello": "John"}
 2       1  {"goodbye": "Moon"}
 ...
100      2  {"also": "Hi"}

I want to be able to ask:

> Give me all the data for job #1

and have the text file automatically built like this:

{"hello": "John"}
{"goodbye": "Moon"}

My concern is if the bytea size is too large, I need to get one-row at a time, and extract the data from it. This seems to work:

    select encode(data, 'escape') from mytable where id='1'

But I would rather not do 2 queries (one to get all rows - and another to get data). Can I issue a single query, and somehow paginate, one row at a time? Number of rows can be large, e.g. 100k.

Any tips to point me in the right direction are much appreciated.

P.S.: Order is not that important to me. E.g. this is ok too (if this helps):

{"goodbye": "Moon"}
{"hello": "John"}
2

Don't store JSON as bytea. Store it as jsonb or json.

Maybe I don't understand your question

ALTER TABLE table
  ALTER COLUMN data
  SET DATA TYPE jsonb USING encode(data, 'escape')::jsonb;

Then just do.. SELECT * FROM table WHERE job_id = 1

My concern is if the bytea size is too large, I need to get one-row at a time, and extract the data from it.

Why? What does too large mean there? The way this works, you'll only encode one row at a time and send it to the client...

  • 1
    Thanks. This works, but I realize now my question was phrased imprecisely. I actually meant that my data field can be binary of any sort, including pdfs, images or other documents. I can see how with json this is the best option. For now - this is all I need anyways. Thank you. – Andriy Drozdyuk Feb 17 '17 at 7:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.