From: | dinesh kumar <dineshkumar02(at)gmail(dot)com> |
---|---|
To: | Seb <spluque(at)gmail(dot)com> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: break table into portions for writing to separate files |
Date: | 2014-05-01 18:20:17 |
Message-ID: | CALnrH7q-tATEGncXTw3498PSbjgr2_5H=iSW8+fOGzrsU1F+iA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
Does the below kind of approach work for you. I haven't tested this, but
would like to give an idea something like below.
Create a plpgsql function which takes 3 parameters as "From Date", "To
Date" and "Interval".
prev_interval := '0'::interval;
LOOP
IF ( "From Date" + "Interval" <= "To Date") THEN
EXECUTE FORMAT (
$$
COPY (SELECT <Columns Llist> FROM <tableName> WHERE timestamp_column >=%s
AND timestamp_column<%s) TO '%s.csv'
$$,
("From Date" + "prev_interval")::TEXT,
("From Date" + "Interval") ::TEXT,
( Filename || (Extract(Epoch from interval)/60)::BIGINT)::TEXT);
prev_interval := "Interval";
"Interval" := "Interval" + "Interval";
ELSE
EXIT FROM LOOP;
END IF;
END LOOP;
Thanks,
Dinesh
manojadinesh.blogspot.com
On Thu, May 1, 2014 at 11:20 PM, Seb <spluque(at)gmail(dot)com> wrote:
> Hello,
>
> I've been looking for a way to write a table into multiple files, and am
> wondering if there are some clever suggestions. Say we have a table
> that is too large (several Gb) to write to a file that can be used for
> further analyses in other languages. The table consists of a timestamp
> field and several numeric fields, with records every 10th of a second.
> It could be meaningfully broken down into subsets of say 20 minutes
> worth of records. One option is to write a shell script that loops
> through the timestamp, selects the corresponding subset of the table,
> and writes it as a unique file. However, this would be extremely slow
> because each select takes several hours, and there can be hundreds of
> subsets. Is there a better way?
>
> Cheers,
>
> --
> Seb
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2014-05-01 18:20:23 | Re: break table into portions for writing to separate files |
Previous Message | Seb | 2014-05-01 17:50:57 | break table into portions for writing to separate files |