Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I installed PostgreSQL Server on my Windows 7 machine. I created database and a simple table, and populated the table. Now I want to connect through the network with my Postgres server and select a given amount of data. I'm not interested of the content of the data, in particular, I can even do this:

select * from table;

but I'm interested in the size of the selected data. Something like this (pseudocode):

select * from table while downloaded_data_size < 3MB

Is that possible? I'm not interested in the content of the data, I just want to select / insert some data, no matter what's inside it, the point is, the selected / inserted data must have a given size.

share|improve this question
2  
What a strange question... select repeat('A',3000000) will give you 3 million bytes to fetch, if that's what you're after. –  Daniel Vérité Jul 3 at 12:13
    
@DanielVérité: Hmm, I guess thats my goal, but I need to do this over the network, so my question is: how can I do this on particular table? –  mirx Jul 3 at 12:46
1  
Connect from your client machine to a PostgreSQL server over the network and the results of that select will go over the network: It's the server that executes the query, not the client. –  Colin 't Hart Jul 3 at 14:10

1 Answer 1

For your purpose @Daniel's simple query should do the trick:

select repeat('A', (3 * 2^20)::int);    -- "3 MB"

"Size" depends. There is the size on disk, with or without overhead, with or without padding, with or without compression, with or without indexes / toast table / free space map ... There is also the size of RAM needed to store the same data (typically more than on disk), the size of the text representation when sent over the wire, the compressed size, with or without tcp/ip overhead ...

You can use the function pg_column_size() to get an approximation.

Retrieve just enough rows from any table that's big enough:

SELECT *
FROM   any_big_tbl
WHERE  big_id <= (
   SELECT big_id
   FROM  (
      SELECT big_id, sum(pg_column_size(b)) OVER (ORDER BY big_id) AS size
      FROM   any_big_tbl b
      ) sub
   WHERE  size > (3 * 2^20)
   ORDER  BY size
   LIMIT  1
   );

big_id being the pk of any_big_tbl.
This computes a running sum of the number of bytes used to store the rows to find the id of the row reaching the requested size. The outer query returns all rows up to that id.

Or, to measure the size of the text representation, including parentheses for the row and escape characters where needed (typically results in fewer rows):

SELECT *
FROM   any_big_tbl
WHERE big_id <= (
   SELECT big_id
   FROM  (
      SELECT big_id, sum(pg_column_size(b::text)) OVER (ORDER BY big_id) AS size
      FROM   any_big_tbl b
      ) sub
   WHERE  size > (3 * 2^20)   -- "3 MB"
   ORDER  BY size
   LIMIT  1
   );

More about measuring "size":
Measure the size of a PostgreSQL table row

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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