How to generate file from postgres data
Hi Team,
I want to execute some queries on postgres and generate the reports
containing the resultset on client side for user in the most efficient way.
The data can be of 1 gb also.
Could you please help.
Thanks and regards,
Shubham
test=# create table t(id int, value text);
CREATE TABLE
test=# insert into t select x, x::text from generate_series(1, 1000) x;
INSERT 0 1000
test=# COPY (select * from t where id < 50 order by id desc) TO
'/tmp/report.csv' DELIMITER ',' CSV HEADER; ---- copy out the results via
a view or sql directly
COPY 49
test=# \q
postgres@db:~/playground$ head -5 /tmp/report.csv
id,value
49,49
48,48
47,47
46,46
and send the csv tar zip compressed etc.
How does your client intend to load 1gb report in a viewer ? paginated ?
all at once ?
correction,
i kind of recollected this as my own problem of a client wanting 100k rows
worth data downloadable.
we finally decided with csv dump and upload to object store, from where the
client would download the file.
all other options like web ui with pagination etc were resulting in memory
issues in the api.
so if it is how to render 1gb db worth data on a web browser, then ignore
my answer.